I've been put in a situation where I have a number of SQL DB's running on 2005/2008 which I have responsibility for. I've been given limited information so am looking at a starting point to determine where I go from here.
I have of course ensured there is a backup strategy in place to secure the data.
We are adding 4-5 indexes to one database and dropping 2 unused indexes. I don't have proper testing environment. How to monitor these indexes changes? Do we need to run any baseline but we don't get load all the time same load all the days
View 8 Replies View RelatedHi, is there a way of how to find all the SQL 2000 servers on our local Network. We have a lot of development servers around the company and I need a tool (or somthing) that could help me find the server name and the version of it.
Thx
Dim
In our system, we need to execute queries using linked servers. However, the linked server name is not well known, it is a value stored in the system. Is there a way this can be done?
CREATE FUNCTION dbo.stfGetSupervisorOrgUnitAssignments(@SupervisorID INT)
RETURNS @ReturnTable TABLE(Org_Unit INT PRIMARY KEY)
AS
BEGIN
DECLARE @Server VARCHAR(100)
SELECT @Server = dbo.stfGetLinkedServerName()
INSERT INTO @ReturnTable(Org_Unit)
SELECT ReplicationID FROM OPENQUERY(@Server, '....')
END
Any help would be greatly appreciated.
I have to set up log shipping from Prod server "A" to 2 different DR servers ("B" and "C")...What do i have to do differently (or additional) using the GUI (ie not using Tsql Scripts) to accomplish this, in addition to the steps that are done to log ship to just one DR server?
View 0 Replies View RelatedI have an SSIS job that dynamically loops through each server, grabbing data for typical DBA reporting, like diskspace, and errorlogs. If the server is down for whatever reason the SSIS package fails. Is there any way I can prevent the SSIS package from failing if one of the servers is down?
View 1 Replies View Relatedi have 70 SQL database servers and i setup DB Mail on the 70 Servers, i want to know is there a way to find the status of all the jobs which i assigned the DB Mail and if its working/failing... is there a script i can run on powershell or SQL to find out that information
View 1 Replies View RelatedBelow is the syntax I am using for creating Linked server from SQL Server i.e windows 2008 R2 standard to Postresql database running on Linux 32 bit Debian (Linux turtle 3.2.0-4-686-pae #1 SMP Debian 3.2.46-1+deb7u1 i686 GNU/Linux) and the version of Postresql is 8.3
/****** Object: LinkedServer [HGCDEV] Script Date: 09/15/2015 17:03:37 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'HGCDEV', @srvproduct=N'', @provider=N'MSDASQL', @datasrc=N'172.16.20.159',@provstr=N'UID=web;PWD=dev123'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'HGCDEV',@useself=N'False',@locallogin=NULL,@rmtuser='web',@rmtpassword='dev123'
This the error I am getting " Cannot initializee the data source object of OLE DB provider "MSDASQL" for linked server "HGCDEV".
How to setup the linked server........... Below are the drivers installed on the SQL server
PostgreSQL35W
PostgreSQL30
I have the default trace on a SQL Server 2008R2 instance enabled and found today that there is a gap of nearly 4 minutes in the trace during a time of the day when there most certainly is not going to be a 4 minute window of nothing.
What if anything could cause the default trace to have a gap like this? The SQL Server Instance (against my preferences) is hosted on VMware however it has its on HOST and so its resources are not being shared with any other server. The data & log files reside on different parts of the SANS. Our IT & Network admins are looking into the issue on their end but when I looked and found a near 4 minute gap in the default trace it hit me that this could be something above/outside of SQL Server.
Is there anyway,can we find the list of servers by querying at active directory?
View 3 Replies View Relatedhow best to approach a problem involving two tables across two different servers.
Table 1: Contains IP Address along with assessment findings. Lets say the fields are IPADDRESSSTR, FINDING
Table 2: Contains Subnet information stored in integer format. The fields are SITE_ID, LOW, and HIGH
What I'd like to do is load the IP range information into memory and then return the findings from table 1 where the IPADDRESSSTR is between the LOW and HIGH integer value.
1) Is there a way to load all of the ranges from table 2 into an array and then compare all the IP addresses (IPADDRESSSTR) from table 1?
2) How do I convert IPADDRESSSTR (a string) to an integer to perform the comparison.
Hi ALL,
I have a 5G database with 1G log file running on SQL server6.5. I would like to establish a short list of performance Monitor objects (NT & SQL server) so that is adequate to capture application performance.
I would like to get some advice:
1.What kind of objects should I monitor on my list?
2.How to automate the process (via a resource kit utility?) to save a new log file off daily and manage an archive process?
3.How to set alerts/notification (but no SQL mail is available now)?
Any help would be appreciated and thanks in advance.
Stella Liu
Hello, I'm running sql2k sp3 on a win2k server. I run the baseline security Analyzer and get yellow (X's) for unchecked buffer in windows help facility, ms02-55.
Flaw in SMB signing could enable group policy to be mofdified, MS02-070
These warnings are telling me that my file versions are greater than expected. the file i have is version 5.2.3718.0 and the one it wants is 5.2.3669.0 and its the same for the other file as well. do i need to fix this?
Sarfaraz writes "I have captured SQL Profiler data. I was reviewing top running CPU intensive SQL statements. The Duration (in seconds) 1.39, 1.09, 0.16 and CPU (in seconds) 0.97, 0.95, 0.16 respectively for some SQL statements. How do I know what is the normal baseline for duration and CPU in order to determine the CPU intensive SQL statements.
Secondly same question for long running procedure duration 0.14, 0.11. What is the normal baseline here. Is this normal or too long.
Thanks,
Sarfaraz"
Hi all,
I want to create a server performance baseline report for my database server. I know I can use System Mointor and SQL Profiler to monitor the server by reading the "SQL Server Books Online". However, there have too many counters, I don't know which counters should I use. If I choose to use the counter, I don't know what is the expect value for the counter.
For example, SQL Server: Buffer Manger Object has almost 22 counters. Which counters should I monitor? If I monitor AWE Lookup Maps/Sec, then what is the expect value for the good performance.
Anyone know a good refer or test book can help me to create a basline report
Thank a lot!
I am training to be a DBA in a company running about 30 machines with MS SQL Server (2000 and 2005). Last week I went to a class where the instructor recommended establishing a performance baseline using windows performance monitor. He also advised to run perfmon remotely so as to not effect the performance.
What I am wondering is since I have so many different machines to baseline, can I run perfmon on one box, using a seperate counter log for each server? I would like to get a nice week-long baseline for each machine, but I also don't want to get bad data by running too many logs at the same time.
My plan is to do a small set of counters for processor, memory, disk, and the SQL server instance(about 10 counters total).
If anyone has experience in this area, I would appreciate any advice that you might have.
Thanks,
Matt
What is the best method of creating schema creation scripts that can bestored into a version control system. The process of using em togenerate a script is not an appealing option. I am still learning theMS Sql sys tables and have not found a useful list of all the codes &types to join the tables etc.mike--Posted via http://dbforums.com
View 2 Replies View Related
Hi
I downloaded MBSA and ran it against my SQL 2005 Server. It tells me that I have a severe risk because
'The following databases have public access.Remove the public access if it is not required - tempdb , model , msdb , ReportServer , ReportServerTempDB'
I have checked these databases and each have the Guest User but it is disabled. If I check the database properties the public role has no permissions against the listed databases.
Is this a bug with MBSA? If not how do I remove Public Access?
Any method on creating baselines for your stored procedures/objects.
View 4 Replies View RelatedI'm trying to run the SQL Server 2012 Best Practices Analyzer. After learning that I first had to install the Microsoft Baseline Configuration Analyzer, I did that.
When I tried to run the Microsoft Baseline Configuration Analyzer/Best Practices Analyzer remotely, though, I got an extremely verbose error message and want to confirm if I really need to do all of the steps involved on each target server that I want to analyze.
Here is what I tried after launching the Microsoft Baseline Configuration Analyzer application (I'm using 'MYSERV' as the target server name):
1. Clicked on "Connect to another computer"
2. Clicked "Another computer:" entered: MYSERV
3. Checked the "Connect as another user:" box
4. Clicked "Set User..." added by Windows credentials.
5. Checked the Use CredSSP box
6. Clicked OK.
After a second or two, the error below came back. Is that what I have to do on each remote computer to run the analyzers?
Microsoft Baseline Configuration Analyzer
Connecting to the remote server failed with the following error message : The WinRM client cannot process this request. CredSSP authentication is currently disabled in the client configuration. Change the client configuration and try the request again. CredSSP authentication must also be enabled in the server configuration. Also, Group Policy must be edited to allow credential delegation to the target computer. Use gpedit.msc and look at the following policy: Computer Configuration -> Administrative Templates -> System -> Credentials Delegation -> Allow Delegating Fresh Credentials. Verify that it is enabled and configured with an SPN appropriate for the target computer. For example, for a target computer name "myserver.domain.com", the SPN can be one of the following; WSMAN/myserver.domain.com or WSMAN/*.domain.com For more information
[URL]
I've been doing a lot of reading on this and my head is starting tohurt! It seems to be quite a feat to work out how much memory isactually being used by our server.I'm running W2K advanced server with SQL 2000 EE, 8GB of RAM, a min of4GB and a max of 6GB is assigned to SQL server.I'm trying to work out whether we've assigned enough or toomuch/little memory to SQL server. My first thought was to let SQLdymanically manage its own memory and see how much it uses, of coursewhen AWE (/3GB /PAE) is enabled it will just use all that isavailable.In perfmon "target server memory" = 6.1GB, "total server memory" =6.1GB, "total pages" = 768000 ( x 8KB = 6.1GB).My second thought was to use "total pages" - the average "free pages"= average mem used, therefore giving me the average amount of memoryused by SQL. I found out that SQL uses a min of 4GB (the min weassigned) and the max of all the memory, 6GB.Is there an easier way of finding out how much memory is actually usedin this situation or is going by the above average the best way?What i'm unsure about is will SQL just use all memory assigned to ituntil it has the whole DB in memory? 20GB including indexes etc....Any help would be greatly apprechiated.
View 2 Replies View RelatedThere are a few features in the new SQL Server - Reporting Services that I really need in production. I have tested everything and it works great. I am running the CTP version since Microsoft is saying they aren't releasing the release version until 3rd quarter 2008.
Since Microsoft won't sell SQL 2008 until 3rd quarter, can I run the CTP in production until the release and then purchase SQL 2008?
Jim
I want two write a small script to determine which is the currently active (primary) server in the AG.
Right now, I see that using SELECT * FROM SYS.dm_hadr_availability_replica_states I can determine the role. However, when the server goes down and switches to the secondary node, I don't believe that the role changes (or does it?). How do I determine which is the active node?
Hello - does anyone have experience w/SQL Server 2005 in a virtual environment? I'm considering this for a production environment but not sure if performance will suffer. Our databases will have a lot of writing but not too much reading. A SSRS solution is currently the only app. connecting to the SQL db. Max users to server at any given time will be very low (~10 users max). But the databases are pulling in data from other, outside multiple data sources on a daily basis.
Any pointers to documentation or any advice?
Thanks,
A Brown
Hello!
Recently, I set up server with Windows Web Server 2008 RC1, SQL 2008 Express beta, .NET 3.5, IIS 7.
I'm running ASP.NET web application with SQL database. Everything works fine until the first application state on the server expires. After that, any postback that starts a new application state on the server and connects to the database, results in the following error:
Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed.
Is this a bug that will be fixed in release of Windows / SQL or am I doing something wrong?
Many thanks for help,
Jan
I must determine FILEGROUP on my DB in SQL Server. Because when i wants to retrieve files which is stored in db, it would be face with error about FILEGROUP.
View 1 Replies View Relatedi must determin FILEGROUP on my DB in SQL Server. because when i wants to retrieve files which is stored in db, it would be face with error about FILEGROUP.
View 1 Replies View RelatedWhat is the official supported way to determine the version of SQL server and installed Service Pack programatically.
Different components such as Analysis Services or SSIS can have service pack applied at different times. So what is the right officially supported way to determine the version of those components.
Thank you in advance.
Is it possible to determine the data flow within a database? e.g. I start by adding/inserting a record in MainTable1, then through a trigger SubTable1, SubTable2 have records inserted into, SubTable3 has a record updated into, then a StoredProcedure1 is run which updates first SubTable4, then a StoredProcedure2 is run which adds a record in SubTable5. So by simply adding a record in MainTable1, I affect 5 other tables.
Imagine a more complex process than this one now (which I'm currently facing and I want to win time and precision; I will still investigate it myself (if I will find an answer first - I will post it here)) Is there a way I could "extract" this flow by using T-SQL?
Hi all,
New to this so please be patient and please help.
I have developed an SQL 2005 Express command line option install for our company which has been working seamlessly for the last 18 months.
http://msdn2.microsoft.com/en-us/library/ms144259.aspx
I downloaded the €œMicrosoft SQL Server 2008 Express CTP, February 2008€? from http://www.microsoft.com/downloads/details.aspx?FamilyId=749BD760-F404-4D45-9AC0-D7F1B3ED1053&displaylang=en
I simply replaced the 2005 file €œSQLEXPR.EXE€? with the 2008 file €œ€?, recompiled the installation and tested only for it to fail. I than read the 2008 books online and noted the change in command line options.
http://www.microsoft.com/downloads/details.aspx?familyid=19DB0B42-A5B2-456F-9C5C-F295CDD58D7A&displaylang=en
http://msdn2.microsoft.com/en-us/library/ms144259(SQL.100).aspx
I then changed the command line to suit the Microsoft 2008 books online, recompiled the installation and tested only for it to fail once more.
Interestingly I tested the install from the default GUI and at the point of adding the €œsa€? login credentials it fails to allow the installation to proceed. Strangely by selecting the windows authentication credentials, €œnext€? than €œback€? it now allows me to add the €œsa€? login credentials and continues to install correctly as required.
I hope I have explained this clearly enough.
1. Is this a bug in the €œMicrosoft SQL Server 2008 CTP, February 2008€? installation?
2. If so is this causing the command line install options to fail?
3. How do I obtain a version of €œMicrosoft SQL Server 2008 Express€? that will work installing from the command line?
Thanks in advance.
I can find many examples of loading DBCC results into tables. They all begin with a create table statement defining the results. My question is , other than trial and error, is there a way to determine what data types will be returned. Sure you can say that first element looks like an integer, but is it really a bigint, and that text string can be varchar(max) but will char(2) work.
I'm not looking for an answer for a specific DBCC function, but rather a generic way I can determine the characteristics of any DBCC result set.
I tried
SELECT *
INTO #tmp
FROM OPENROWSET('SQLOLEDB',
'Server=ray;Trusted_Connection=Yes;Database=Ed_sandbox',
'Set FmtOnly OFF; DBCC loginfo WITH tableresults ')
but I got back
Msg 11527, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1
The metadata could not be determined because statement 'DBCC loginfo WITH tableresults' does not support metadata discovery.
I create a job name test connection by SQL AGent
Background:
- Have server1 and server2
- The job has 3 steps:
 + Step1: check server1 is running, next step2
              Server1 is shutdown or can not ping, next step3
 + Step2: do anything, for Example: run batch exe on server1
 + Step3: do anything, for Example: run batch exe on server2
Step1, i am using ping server1 command,
My problem is if server1 is shutdown, my command (ping server1) is also return true
and in the fact, job is run by follow Step1 -> Step2.
Expectation the job is run by follow step1 -> Step3
I have visual studio 2005 with XP home. I'm trying to find out which service pack my SQL server is running as I've been told that Service pack 2 has a few bugs.
For example ADO Applications Report a Syntax Error When Column Names Contain Spaces. This appears to be an error i am recieving. I just wanted to confirm that it is a microsoft bug rather then something Im doing wrong.
Microsoft meantions the bug here: http://support.microsoft.com/kb/264687
I've been looking everywhere. I do not have the "query analyzer window" where you can find this out as I have VS 2005. How would I find this out.
Thank you