tag:blogger.com,1999:blog-85761092618865964152024-03-17T00:22:51.946+05:30Shashank Srivastava (SQL DBA)Hi all,
I am Shashank Srivastava currently working as a SQL DBA for a client that has one of the best database environment in the world. I have two years of experience and just wanted to learn and share through the blog. This blog is just to share my knowledge with all the people and learn from them.
I have completed Microsoft Certified Technology Specialist -MS SQL SERVER 2005 , Microsoft Certified Technology Specialist -MS SQL SERVER 2008 and ITIL v3 Certified IT professional certifications.Anonymoushttp://www.blogger.com/profile/01716610258406540066noreply@blogger.comBlogger71125tag:blogger.com,1999:blog-8576109261886596415.post-975465549703622002013-03-08T12:18:00.000+05:302013-03-08T12:18:08.627+05:30How to extract Litespeed file and convert it into native backup file<div dir="ltr" style="text-align: left;" trbidi="on">
Please find the extractor.exe file on the system and then go to the path using CMD, once you have reached to the location, use the below command.<br />
<br />
<br />
Extractor.exe -F Sourcepath\filename.BAK -E Destinationpath\filename_native.bak<br />
<br />
If you don't have extractor.exe then copy the same from any server to the destination server and then try to extract it.<br />
<div>
<br /></div>
</div>
Anonymoushttp://www.blogger.com/profile/01716610258406540066noreply@blogger.com4tag:blogger.com,1999:blog-8576109261886596415.post-72968411757638417622013-03-08T12:15:00.001+05:302013-03-08T12:15:18.430+05:30How to restore database using Litespeed backup file and TSQL command<div dir="ltr" style="text-align: left;" trbidi="on">
Below is TSQL script to restore database in MSSQL using Litespeed backup file.<br />
<br />
exec master.dbo.xp_restore_database @database = N'databasename' ,<br />
<br />
@filename = N'Full path of the backup file',<br />
@filenumber = 1,<br />
@with = N'STATS = 10',<br />
@with = N'MOVE N''LogicalFileNameOf MDF file'' TO N''Destinationpath\Logicalfilename.mdf''',<br />
@with = N'MOVE N''LogicalFileNameOf LDF file'' TO N''Destinationpath\Logicalfilename.ldf''',<br />
@affinity = 0,<br />
@logging = 0<br />
GO<br />
<br />
Example :-<br />
<br />
<br />
exec master.dbo.xp_restore_database @database = N'Test' ,<br />
@filename = N'Q:\Backup\Test_20130308.bak',<br />
@filenumber = 1,<br />
@with = N'STATS = 10',<br />
@with = N'MOVE N''Test'' TO N''M:\data\Test.mdf''',<br />
@with = N'MOVE N''Test_log'' TO N''H:\logs\Test_log.ldf''',<br />
@affinity = 0,<br />
@logging = 0<br />
GO<br />
<div>
<br /></div>
<br />
<div>
<br /></div>
</div>
Anonymoushttp://www.blogger.com/profile/01716610258406540066noreply@blogger.com10tag:blogger.com,1999:blog-8576109261886596415.post-50716407033880086872013-02-27T14:34:00.001+05:302013-02-27T14:34:05.611+05:30Script to shrink all DB's with in SQL server 2005.<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
SET NOCOUNT ON<br />
<br />
DECLARE @DBName varchar(100)<br />
DECLARE @FileName varchar(100)<br />
DECLARE @TempTxt varchar(1000)<br />
DECLARE @Cnt smallint<br />
<br />
SET @TempTxt = 'USE Master;' + CHAR(13) + <br />
'EXEC xp_fixeddrives;'<br />
<br />
DECLARE cmdtxt_cursor CURSOR FOR SELECT RTRIM(master..Sysdatabases.Name) as [DBName],<br />
RTRIM(master..Sysaltfiles.Name) as [FileName]<br />
FROM master..Sysdatabases<br />
Inner join master..Sysaltfiles on master..Sysaltfiles.DBID = master..Sysdatabases.DBID<br />
WHERE master..Sysdatabases.DBID not in (1,2,3,4) and Patindex('%log%', master..Sysaltfiles.Name) > 0<br />
and ((master..Sysaltfiles.Size)*8/1024) > 0 and Patindex ('% %', master..Sysdatabases.Name) = 0<br />
ORDER BY Left(master..Sysaltfiles.FileName, 1)<br />
<br />
SET @TempTxt = 'USE Master;' + CHAR(13) + <br />
'EXEC xp_fixeddrives;'<br />
--PRINT @TempTxt<br />
EXEC (@TempTxt)<br />
<br />
OPEN cmdtxt_cursor<br />
FETCH NEXT FROM cmdtxt_cursor INTO @DBName, @FileName<br />
WHILE @@FETCH_STATUS = 0<br />
BEGIN<br />
--PRINT 'Processing : ' + @DBName<br />
If Exists(Select master..Sysdatabases.[Name] From master..Sysdatabases<br />
Where master..Sysdatabases.[Name] = @DBName)<br />
BEGIN<br />
SET @TempTxt = 'USE [' + @DBName + '];' + CHAR(13) +<br />
'BACKUP LOG [' + @DBName + '] WITH TRUNCATE_ONLY ;' + CHAR(13) + <br />
'DBCC SHRINKFILE (''' + @FileName + ''', 1);'<br />
--PRINT @TempTxt<br />
EXEC (@TempTxt)<br />
FETCH NEXT FROM cmdtxt_cursor INTO @DBName, @FileName<br />
END<br />
END<br />
<br />
CLOSE cmdtxt_cursor<br />
DEALLOCATE cmdtxt_cursor<br />
SET @TempTxt = 'USE Master;' + CHAR(13) + <br />
'EXEC xp_fixeddrives;'<br />
--PRINT @TempTxt<br />
EXEC (@TempTxt)<br />
GO<br />
<br />
</div>
Anonymoushttp://www.blogger.com/profile/01716610258406540066noreply@blogger.com1tag:blogger.com,1999:blog-8576109261886596415.post-72357774153344382582013-02-27T14:31:00.002+05:302013-02-27T14:31:19.617+05:30Error Code 1326 - Not able to execute xp_cmdshell<div dir="ltr" style="text-align: left;" trbidi="on">
Issue :- A domain SQL service account password has been changes and after that the user is complaining that he is not able to access xp_cmdshell.<br />
<br />
Error 1326 was seen.<br />
<br />
Resolution :- We checked the SQL server properties and found that the same account was being used as a proxy account in SQL server. Updated the new password of the service account at proxy account and then it was able to access the xp_cmdshell.</div>
Anonymoushttp://www.blogger.com/profile/01716610258406540066noreply@blogger.com0tag:blogger.com,1999:blog-8576109261886596415.post-75533634091503078892013-02-27T14:24:00.001+05:302013-02-27T14:24:19.100+05:30Script to grant database level role to a login on all user database<div dir="ltr" style="text-align: left;" trbidi="on">
Below script will grant db_owner role to a newly created login on all user databases. We can use the same script to grant any database level role to the login on all user database.<br />
<br />
Script 1 :- For Windows Login<br />
<br />
create login loginname from windows<br />
<br />
DECLARE @command varchar(1000)<br />
SELECT @command = 'IF ''?'' NOT IN(''master'', ''msdb'', ''tempdb'',''model'')<br />
BEGIN USE ? EXEC(''create user username for login loginname'') END '<br />
<br />
EXEC sp_MSforeachdb @command<br />
<br />
DECLARE @command1 varchar(1000)<br />
<br />
Select @command1 = 'IF ''?'' NOT IN(''master'', ''msdb'', ''tempdb'',''model'')<br />
BEGIN USE ? EXEC sp_addrolemember ''db_owner'', ''username'' END'<br />
<br />
EXEC sp_MSforeachdb @command1<br />
<br />
<br />
For SQL Login<br />
<br />
create login loginname with password = ' Enter the password for the login'<br />
<br />
DECLARE @command varchar(1000)<br />
SELECT @command = 'IF ''?'' NOT IN(''master'', ''msdb'', ''tempdb'',''model'')<br />
BEGIN USE ? EXEC(''create user username for login loginname'') END '<br />
<br />
EXEC sp_MSforeachdb @command<br />
<br />
DECLARE @command1 varchar(1000)<br />
<br />
Select @command1 = 'IF ''?'' NOT IN(''master'', ''msdb'', ''tempdb'',''model'')<br />
BEGIN USE ? EXEC sp_addrolemember ''db_owner'', ''username'' END'<br />
<br />
EXEC sp_MSforeachdb @command1<br />
<br />
<br />
<br />
<br />
<br /></div>
Anonymoushttp://www.blogger.com/profile/01716610258406540066noreply@blogger.com1tag:blogger.com,1999:blog-8576109261886596415.post-51980778872720232492012-11-29T19:33:00.000+05:302012-11-29T19:33:41.577+05:30How to resolve missing MSI and MSP files error while patching SQL SERVER<div dir="ltr" style="text-align: left;" trbidi="on">
Many times while patching SQL SERVER we have come across issues of missing MSI and MSP files. Lets get an in depth details of what there files are and how to resolve this issue.<br />
<br />
Q 1. What are these MSI and MSP files ?<br />
<br />
MSI files are those SQL files that are shipped with the RTM verison or base version of SQL server. <br />
<br />
They can be find by navigating to x64 -> Setup folder in your installation media if you are searching for 64 bit or x86 -> Setup folder if the search if for 32 bit files.<br />
<br />
MSP files are those SQL files that are shipped with the SQL SERVER patches are they are not related to base verison.<br />
<br />
For viewing the files extract the Service pack and navigate to x64 -> Setup folder in your extracted files if you are searching for 64 bit or for 32 bit extract service pack for 32 bit and navigate to x86 -> Setup folder if the search if for 32 bit files.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiDrUXiNd1S2Ywe3SqYRBGErjTdfXOz_N-knMxXFMNhwf9uvdkWVlgcoyVNyG5Oh4JSLqs037ZtvxeGMIlXsgVBIKRSuBOZICEs50fuM3ZzGlBfv7euOGpA5g5E30cENibf4i2nH1ns3sM/s1600/MSIfiles.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiDrUXiNd1S2Ywe3SqYRBGErjTdfXOz_N-knMxXFMNhwf9uvdkWVlgcoyVNyG5Oh4JSLqs037ZtvxeGMIlXsgVBIKRSuBOZICEs50fuM3ZzGlBfv7euOGpA5g5E30cENibf4i2nH1ns3sM/s400/MSIfiles.jpg" width="347" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEix-J4zaxB6ZEbqI1b6RwQ0BevH4OwNlmUP0_3G203oH6HqeuNk5rVuxib-O5TOKhUheb7VhTDgRa4VWnvfvJJaU0LeTbaDoA5Di-2z1p3dr3aF9pKxssZ9sJeiKMsPLckmRbNk5-EviN8/s1600/MSPfiles.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="261" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEix-J4zaxB6ZEbqI1b6RwQ0BevH4OwNlmUP0_3G203oH6HqeuNk5rVuxib-O5TOKhUheb7VhTDgRa4VWnvfvJJaU0LeTbaDoA5Di-2z1p3dr3aF9pKxssZ9sJeiKMsPLckmRbNk5-EviN8/s400/MSPfiles.jpg" width="400" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
Example - Setup failed for Sqlrun_sql.msi file</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
For viewing the error go to C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log location and search for the latest logs.</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
Open the log file and you can view details in the log, looking similar like below log.</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
</div>
MSI (c) (AC:D0) [17:45:28:483]: Resetting cached policy values<br />
MSI (c) (AC:D0) [17:45:28:483]: Machine policy value 'Debug' is 0<br />
MSI (c) (AC:D0) [17:45:28:483]: ******* RunEngine:<br />
******* Product: {4D28EFCF-5999-44D2-8D4E-AC643E76C33F}<br />
******* Action: <br />
******* CommandLine: **********<br />
MSI (c) (AC:D0) [17:45:28:483]: Client-side and UI is none or basic: Running entire install on the server.<br />
MSI (c) (AC:D0) [17:45:28:483]: Grabbed execution mutex.<br />
MSI (c) (AC:D0) [17:45:28:515]: Cloaking enabled.<br />
MSI (c) (AC:D0) [17:45:28:515]: Attempting to enable all disabled priveleges before calling Install on Server<br />
MSI (c) (AC:D0) [17:45:28:530]: Incrementing counter to disable shutdown. Counter after increment: 0<br />
MSI (s) (D0:F8) [17:45:28:546]: Grabbed execution mutex.<br />
MSI (s) (D0:F4) [17:45:28:546]: Resetting cached policy values<br />
MSI (s) (D0:F4) [17:45:28:546]: Machine policy value 'Debug' is 0<br />
MSI (s) (D0:F4) [17:45:28:546]: ******* RunEngine:<br />
<strong>******* Product: {4D28EFCF-5999-44D2-8D4E-AC643E76C33F}</strong><br />
******* Action: <br />
******* CommandLine: **********<br />
MSI (s) (D0:F4) [17:45:28:546]: Machine policy value 'DisableUserInstalls' is 0<br />
MSI (s) (D0:F4) [17:45:28:546]: Adding MSIINSTANCEGUID to command line.<br />
<strong>MSI (s) (D0:F4) [17:45:28:546]: Warning: Local cached package 'C:\WINDOWS\Installer\258fh9.msi' is missing.</strong><br />
<strong><br /></strong>
<strong>Issue :- </strong>The very first error I see in the above log is <strong>Local cached package 'C:\WINDOWS\Installer\</strong><strong>258fh9</strong><strong>.msi' is missing</strong>. This file <strong>258fh9.</strong>msi is the cached msi file for sqlrun_sql.msi i.e. the main installation (RTM) of an instance of SQL Server Database Engine. During installation of RTM product, ..\Servers\Setup\SqlRun_SQL.msi is cached to %windir%\Installer folder for future use such as un-installation etc. Cached file has a different name which is a randomized alphanumeric name generated by the installer , this is because if you have multiple instances then multiple ‘Sqlrun_sql.msi’ has to be cached and hence a name conflict may occur.<br />
<br />
Resolution :-<br />
<br />
Step 1:- Open REGEDIT.<br />
<br />
Step 2 :- Navigate to<br />
<br />
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Installer\UserData\S-1-5-18\Products\<b><GUID></b>\InstallProperties<br />
<br />
Here GUID is :- <strong>4D28EFCF-5999-44D2-8D4E-AC643E76C33F</strong><br />
<strong><br /></strong>
<strong>Now look for value of Local Package.</strong><br />
<strong><br /></strong>
Step 3 :- Now that we knew 'C:\WINDOWS\Installer\<strong>258fh9</strong>.msi' is missing , we need to put it back to C:\Windows\Installer. To do so , locate the file from the installation media of SQL Server , copy Servers\Setup\SqlRun_SQL.msi and paste it into C:\Windows\Installer , rename it to the cached filename , in our case we have to rename it to <strong>258fh9</strong>.msi.<br />
<br />
Now re-run the setup and it will execute successfully.<br />
<br />
<br />
For Resolving the MSP file errors follow the below steps :-<br />
<br />
1. Check the Product GUID from the log.<br />
2. Check the Patch GUID from the log.<br />
3. Open regedit<br />
4. Navigate to below path<br />
<br />
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Installer\UserData\S-1-5-18\Products\<GUID>\Patches\<PatchCode><br />
<br />
Where GUID and Patch Code will be gathered from logs.<br />
<br />
5. Then see the Display name and it will give the name of the service pack whose file is missing.<br />
<br />
6. Extract the same service pack.<br />
<br />
7. Copy the missing file and replace it by the number.msp where number can be seen in the errorlog.<br />
<br />
If yor are not able to find the number then navigate to<br />
<br />
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Installer\UserData\S-1-5-18\Patches\<PatchCode><br />
<br />
And search for the patch code from the error log, and under local package you can see the filenumber.msp. Replace the file with the same number and run the setup again.<br />
<br />
<br />
<strong><br /></strong>
<br />
<br /></div>
Anonymoushttp://www.blogger.com/profile/01716610258406540066noreply@blogger.com1tag:blogger.com,1999:blog-8576109261886596415.post-43642314293683023812012-10-11T10:43:00.000+05:302012-10-11T10:43:05.702+05:30Script to grant database level role to a login on all user database<div dir="ltr" style="text-align: left;" trbidi="on">
Below script will grant db_owner role to a newly created login on all user databases. We can use the same script to grant any database level role to the login on all user database.<br />
<br />
Script 1 :- For Windows Login<br />
<br />
<br />
<br />
create login Servername\LoginName from windows<br />
<br />
DECLARE @command varchar(1000)<br />
SELECT @command = 'IF ''?'' NOT IN(''master'', ''msdb'', ''tempdb'',''model'')<br />
BEGIN USE ? EXEC(''create user Servername\LoginName for login Servername\LoginName'') END '<br />
<br />
EXEC sp_MSforeachdb @command<br />
<br />
DECLARE @command1 varchar(1000)<br />
<br />
Select @command1 = 'IF ''?'' NOT IN(''master'', ''msdb'', ''tempdb'',''model'')<br />
BEGIN USE ? EXEC sp_addrolemember ''db_owner'', ''Servername\LoginName'' END'<br />
<br />
EXEC sp_MSforeachdb @command1<br />
<br />
<br />
E.g. :-<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjGVvtxyDeIgGLJRCsSLd-69dEz9fHfBbQAOHbl9ML7z75eV93adxKpLMEp33OFsvyPPPOH1MODCCu4W_91zIOjpKTG044GTohmh5oYJqvhIQkpasBbWZNENt4SHVDxeB_uu3PaRYcHGJI/s1600/db_ownerscript.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="294" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjGVvtxyDeIgGLJRCsSLd-69dEz9fHfBbQAOHbl9ML7z75eV93adxKpLMEp33OFsvyPPPOH1MODCCu4W_91zIOjpKTG044GTohmh5oYJqvhIQkpasBbWZNENt4SHVDxeB_uu3PaRYcHGJI/s640/db_ownerscript.jpg" width="640" /></a></div>
For SQL Logins :-<br />
<br />
<br />
<br />
create login loginname with password ='Give the password for SQL Login'<br />
<br />
DECLARE @command varchar(1000)<br />
SELECT @command = 'IF ''?'' NOT IN(''master'', ''msdb'', ''tempdb'',''model'')<br />
BEGIN USE ? EXEC(''create user username for login loginname'') END '<br />
<br />
EXEC sp_MSforeachdb @command<br />
<br />
DECLARE @command1 varchar(1000)<br />
<br />
Select @command1 = 'IF ''?'' NOT IN(''master'', ''msdb'', ''tempdb'',''model'')<br />
BEGIN USE ? EXEC sp_addrolemember ''db_owner'', ''username'' END'<br />
<br />
EXEC sp_MSforeachdb @command1<br />
<br />
<br />
If you want to create the same login on mutiple server and assign it db_owner rights on all user databases then save the required script as dbownerscript.sql in C drive under SQL folder and open the CMD prompt.<br />
<br />
On the cmd prompt use the below command for each server and it will execute the script the on that particular server.<br />
<br />
sqlcmd -S Servername\InstanceName -i C:\sql\dbownerscript.sql<br />
<br />
<br />
<br />
</div>
Anonymoushttp://www.blogger.com/profile/01716610258406540066noreply@blogger.com10tag:blogger.com,1999:blog-8576109261886596415.post-73376019750669564512012-10-06T11:50:00.001+05:302012-10-06T11:50:59.491+05:30How to create a Linked server in SQL SERVER<div dir="ltr" style="text-align: left;" trbidi="on">
Follow the below steps to create a Linked server in SQL SERVER.<br />
<br />
Prerequisite for creation of linked server -<br />
<br />
1. Connectivity should be there between the two servers and port 1433 or the port on which destination <br />
SQL SERVER is running should be open in firewall on destination server from source server so that<br />
connection can be established from source to destination server.<br />
<br />
2. Account that will be used for connecting from source to destination server should be added in the<br />
destination server with the required rights.<br />
<br />
<br />
Step 1 : - Connect to source server and go to Server objects -> Linked Server -> New Linked Server<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgZvf79Yiv9xfZa2wP8pqycuCut0_TLEQ2jMdqz0Txq-ki8ZhuoOBrAr9tkOR5QmjCNbSpuXY0IRPwOinPZfv0NCw8UFz_N4e14KgXmRTdEEN81aQNFTzHXHJYBqIWA0wFBkpX-cX8iX_I/s1600/linkedserver1.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgZvf79Yiv9xfZa2wP8pqycuCut0_TLEQ2jMdqz0Txq-ki8ZhuoOBrAr9tkOR5QmjCNbSpuXY0IRPwOinPZfv0NCw8UFz_N4e14KgXmRTdEEN81aQNFTzHXHJYBqIWA0wFBkpX-cX8iX_I/s640/linkedserver1.jpg" width="640" /></a></div>
<br />
<br />
Step 2 :- If your destination server is SQLSERVER then mention the destination server name in the Linked server name. If the Server is running on any other port other than 1433 then please mention the port number also ( Server Name, Port Number).<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEioabxY74ArNCgYYnSQO9ayXExgTYUXo-6IuoufqQNfxLgmAoKVRg5Xbv9USX28hcb9ru2XpRRRLIiKAR8QlsA8r9NX43VWm7nx3i_Q0VZiVpTccZKOhDr8pqA-NK2My5uJ2PX9zyp8U9A/s1600/linkedserver2.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEioabxY74ArNCgYYnSQO9ayXExgTYUXo-6IuoufqQNfxLgmAoKVRg5Xbv9USX28hcb9ru2XpRRRLIiKAR8QlsA8r9NX43VWm7nx3i_Q0VZiVpTccZKOhDr8pqA-NK2My5uJ2PX9zyp8U9A/s640/linkedserver2.jpg" width="640" /></a></div>
<br />
<br />
Step 3 : - Enter the login credentials that will be used to connect to destination SQL SERVER. This will specify how the linked server will be authenticated.<br />
<br />
<br />
<dt style="font-family: 'Segoe UI', Verdana, Arial; font-size: 13px;"><span class="label" style="font-weight: bold;">Not be made</span></dt>
<dt style="font-family: 'Segoe UI', Verdana, Arial; font-size: 13px;"><br /></dt>
<dt style="font-family: 'Segoe UI', Verdana, Arial; font-size: 13px;">Specify that a connection will not be made for logins not defined in the list.</dt>
<dd style="font-family: 'Segoe UI', Verdana, Arial; font-size: 13px;"><br /></dd>
<dt style="font-family: 'Segoe UI', Verdana, Arial; font-size: 13px;"><span class="label" style="font-weight: bold;">Be made without using a security context</span></dt>
<dt style="font-family: 'Segoe UI', Verdana, Arial; font-size: 13px;"><br /></dt>
<dt style="font-family: 'Segoe UI', Verdana, Arial; font-size: 13px;">Specify that a connection will be made without using a security context for logins not defined in the list.</dt>
<dt style="font-family: 'Segoe UI', Verdana, Arial; font-size: 13px;"><span class="label" style="font-weight: bold;"><br /></span></dt>
<dt style="font-family: 'Segoe UI', Verdana, Arial; font-size: 13px;"><span class="label" style="font-weight: bold;">Be made using the login's current security context</span></dt>
<dt style="font-family: 'Segoe UI', Verdana, Arial; font-size: 13px;"><br /></dt>
<dt style="font-family: 'Segoe UI', Verdana, Arial; font-size: 13px;">Specify that a connection will be made using the current security context of the login for logins not defined in the list. If connected to the local server using Windows Authentication, your windows credentials will be used to connect to the remote server. If connected to the local server using SQL Server Authentication, login name and password will be used to connect to the remote server. In this case a login with the exact same name and password must exist on the remote server.</dt>
<dt style="font-family: 'Segoe UI', Verdana, Arial; font-size: 13px;"><span class="label" style="font-weight: bold;"><br /></span></dt>
<dt style="font-family: 'Segoe UI', Verdana, Arial; font-size: 13px;"><span class="label" style="font-weight: bold;">Be made using this security context</span></dt>
<dt style="font-family: 'Segoe UI', Verdana, Arial; font-size: 13px;"><br /></dt>
<dt style="font-family: 'Segoe UI', Verdana, Arial; font-size: 13px;">Specify that a connection will be made using the login and password specified in the <span class="label" style="font-weight: bold;">Remote login</span> and <span class="label" style="font-weight: bold;">With password</span> boxes for logins not defined in the list. The remote login must be a SQL Server Authentication login on the remote server.</dt>
<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjmcXpHFbqKAA_v0lHbWPmq0yraQX5o6jVEjzplDD7nDddRBhjDtPhZFl0g2xBF96pQCwrRA8qigNwlWE2wIXq5JwFPkukAKZHm95O-45JSCT2ZtIN6sJivtlXMqoN5vtKfwi1CdaVEWtw/s1600/linkedserver3.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjmcXpHFbqKAA_v0lHbWPmq0yraQX5o6jVEjzplDD7nDddRBhjDtPhZFl0g2xBF96pQCwrRA8qigNwlWE2wIXq5JwFPkukAKZHm95O-45JSCT2ZtIN6sJivtlXMqoN5vtKfwi1CdaVEWtw/s640/linkedserver3.jpg" width="640" /></a></div>
<br />
Step 4 :- Click OK and linked server is created. You can view the linked server at Server Objects -> Linked Server.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh6vWrqSXFT_X3hrd7mtsQKa65VEaneo3CLZ36dTo6IN287RSd5RIvgX3Lhvs4w7wueeaQBoUjaNpEGFNK9F6seOMDKMZkVRTB1VMp-gqQ7deLyz8w9d0_rccIAb8Amfbr7k2ZEyHUkX2g/s1600/linkedserver4.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh6vWrqSXFT_X3hrd7mtsQKa65VEaneo3CLZ36dTo6IN287RSd5RIvgX3Lhvs4w7wueeaQBoUjaNpEGFNK9F6seOMDKMZkVRTB1VMp-gqQ7deLyz8w9d0_rccIAb8Amfbr7k2ZEyHUkX2g/s640/linkedserver4.jpg" width="640" /></a></div>
<br />
Step 5 :- Verify if the linked server created is able to access the destination server from source server. Right click the linked server and select test connection. It will be successful if it show connection succeeded.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhso6FWv8JGPX3BazOg1RU35AgPX3cndKLb-9q6ucbDshHuVsQIGsLF2DBGSTT1zXK8ChnBjQPnCVrM9D17roep2izPCr5MLwZcfPWgZhqIaeETfbX4OA8SKAerITouhN4AddX0M-mXYEU/s1600/linkedserver5.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhso6FWv8JGPX3BazOg1RU35AgPX3cndKLb-9q6ucbDshHuVsQIGsLF2DBGSTT1zXK8ChnBjQPnCVrM9D17roep2izPCr5MLwZcfPWgZhqIaeETfbX4OA8SKAerITouhN4AddX0M-mXYEU/s640/linkedserver5.jpg" width="640" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgwjd62R_5yKwydt-jp4sgzyLcfj6tmI61nLOAIXVxRBAqapepNSO8QuEcmG3dmqzCt1S6LtYsJOzDBMkdU3MidE90w62dPQnAXHYBLXdemaCOuFHDxF0YNldWs0RbUygegwQmbKQVnNoE/s1600/linkedserver6.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgwjd62R_5yKwydt-jp4sgzyLcfj6tmI61nLOAIXVxRBAqapepNSO8QuEcmG3dmqzCt1S6LtYsJOzDBMkdU3MidE90w62dPQnAXHYBLXdemaCOuFHDxF0YNldWs0RbUygegwQmbKQVnNoE/s640/linkedserver6.jpg" width="640" /></a></div>
<br />
<br />
Step 6 :- For querying the destination server name use the FQDN name.<br />
<br />
FQDN name is ServerName.DatabaseName.SchemaName.ObjectName.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi7nOnXAK7X4o7Oh0a79s71o4kOAxk5RbWwWXf22p-kE8lap0wm4IcyCEHY-ghI4-8GFTno6aUKylYlidxCmxw6iU7awYPY5SXVYmt9qqaCTG22cwjT5FCB7Ov19WdhkiNo9XAeTmWyXto/s1600/linkedserver7.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi7nOnXAK7X4o7Oh0a79s71o4kOAxk5RbWwWXf22p-kE8lap0wm4IcyCEHY-ghI4-8GFTno6aUKylYlidxCmxw6iU7awYPY5SXVYmt9qqaCTG22cwjT5FCB7Ov19WdhkiNo9XAeTmWyXto/s640/linkedserver7.jpg" width="640" /></a></div>
<br /></div>
Anonymoushttp://www.blogger.com/profile/01716610258406540066noreply@blogger.com0tag:blogger.com,1999:blog-8576109261886596415.post-53969493236073397622012-10-03T22:13:00.000+05:302012-10-03T22:13:07.854+05:30How to change the SQL SERVER name after changing the windows machine name<div dir="ltr" style="text-align: left;" trbidi="on">
When we change the name of the windows machine on whom the SQL Server is running, then the SQL server name does not gets change. We need to manually add the new server name and remove the old server name.<br />
<br />
Issue :- We have changed the windows machine name from Ahsi-PC to Shashank but when we look for the SQL Server name it will give the old windows machine name.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEioQ6vJRCfbHIl50uVc3KXUwsemzjeqTHEp3lFylOjx4Yj8sT7gvCeZcDi2o-GHxlL4emASXn7W_RDMcCohnKMV9UA-UxUWwKqXAT0YlfI6M4BtRCGPVsCLjF2Uhpasy296WsWTTG8GIio/s1600/renameserver1.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="502" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEioQ6vJRCfbHIl50uVc3KXUwsemzjeqTHEp3lFylOjx4Yj8sT7gvCeZcDi2o-GHxlL4emASXn7W_RDMcCohnKMV9UA-UxUWwKqXAT0YlfI6M4BtRCGPVsCLjF2Uhpasy296WsWTTG8GIio/s640/renameserver1.jpg" width="640" /></a></div>
<br />
<br />
Step 1 : Run sp_dropserver 'Old server Name'on master database<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgL7cL-QN9I1Y2tHWLwH26Yn2eWrB0INykSAG7FS_NVSrWG3zP0mZNHDNEPoISXEGZPv7THRdjnovFtlh9PpiPkOZltHPQFBN2yc7k5eub89HZ164QXOPtGvEU2T7g6t2M5Vc6-ZsrVIX0/s1600/renameserverdrop.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="412" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgL7cL-QN9I1Y2tHWLwH26Yn2eWrB0INykSAG7FS_NVSrWG3zP0mZNHDNEPoISXEGZPv7THRdjnovFtlh9PpiPkOZltHPQFBN2yc7k5eub89HZ164QXOPtGvEU2T7g6t2M5Vc6-ZsrVIX0/s640/renameserverdrop.jpg" width="640" /></a></div>
<br />
Step 2 : - Run sp_addserver 'New server name','local' on master database. After running restart the SQL services.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhmAbpyvnSeE_UbpTNgn3i5ePPiYWLkx-2xwd4ryhiWSnlH0S3buSAIsqw_ifHCPAmq6VHEj_bbFGc5_M2z06PHr50PuFKzqKu3IqC_dK9WaeRZSF2H0S__CkQ7ydQ88e6vKkt3rFBVwvo/s1600/renameserveradd.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="406" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhmAbpyvnSeE_UbpTNgn3i5ePPiYWLkx-2xwd4ryhiWSnlH0S3buSAIsqw_ifHCPAmq6VHEj_bbFGc5_M2z06PHr50PuFKzqKu3IqC_dK9WaeRZSF2H0S__CkQ7ydQ88e6vKkt3rFBVwvo/s640/renameserveradd.jpg" width="640" /></a></div>
<br />
Step 3 :- Check the new name of the instance by running select @@servername.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgkFeem7WyNjeOiHPZv0N2ch4OMXQQgsci1t3JW36YpwVjX9kGJJOiQmVBjZM8KNNBVPyOXgttnbXl9pUOB74xiiho6c5t8QUGxaVE6Hg46Jl_cMVYUpWzsMceGiDdIOOsOBUusKC6CpRQ/s1600/renameserverfinal.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="444" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgkFeem7WyNjeOiHPZv0N2ch4OMXQQgsci1t3JW36YpwVjX9kGJJOiQmVBjZM8KNNBVPyOXgttnbXl9pUOB74xiiho6c5t8QUGxaVE6Hg46Jl_cMVYUpWzsMceGiDdIOOsOBUusKC6CpRQ/s640/renameserverfinal.jpg" width="640" /></a></div>
<br />
Now the SQL instance name has been changed after windows name change. Minimum rights required to perform the above action is setupadmin server role.</div>
Anonymoushttp://www.blogger.com/profile/01716610258406540066noreply@blogger.com0tag:blogger.com,1999:blog-8576109261886596415.post-44431904037011638422012-10-02T00:37:00.002+05:302012-10-02T00:37:37.527+05:30Script to find all details of SQL Server 2005/2008/2008R2<div dir="ltr" style="text-align: left;" trbidi="on">
The below script will provide all the details of the SQL Server 2005/2008/2008R2. This script can be very useful when you are taking the server in support and you want to keep a record of the server details at the time of possession.<br />
<br />
<br />
--------------------------------------------------------<br />
<br />
SET NOCOUNT ON<br />
use master<br />
go<br />
<br />
print '***************************************************************'<br />
print ' MANUAL ACTIVITIES '<br />
print ' '<br />
print ' A. See database startup parameters '<br />
print ' B. See SQL Server Error Log and NT Event Viewer '<br />
print ' C. See authentication mode ( NATIVE or MIXED ) '<br />
print ' D. See SQL Server and SQL Agent services account startup '<br />
print ' E. See SQL Mail configuration <span class="Apple-tab-span" style="white-space: pre;"> </span> '<br />
print ' F. See backup politic ( full and transaction ) '<br />
print '***************************************************************'<br />
<br />
print ''<br />
print '1. General Info'<br />
print '*********************'<br />
print ''<br />
<br />
print 'Server Name...............: ' + convert(varchar(30),@@SERVERNAME)<br />
print 'Instance..................: ' + convert(varchar(30),@@SERVICENAME)<br />
print 'Current Date Time.........: ' + convert(varchar(30),getdate(),113)<br />
print 'User......................: ' + USER_NAME()<br />
go<br />
<br />
print ''<br />
print '1.1 Database and Operational System versions.'<br />
print '----------------------------------------------'<br />
print ''<br />
<br />
select @@version<br />
go<br />
<br />
exec master..xp_msver<br />
go<br />
<br />
print ''<br />
print'*****************************'<br />
<br />
SELECT createdate as Sql_Server_Install_Date<br />
FROM sys.syslogins<br />
where name = 'NT AUTHORITY\SYSTEM'<br />
<br />
print ''<br />
print '1.2 Miscelaneous'<br />
print '---------------------------'<br />
print ''<br />
<br />
select convert(varchar(30),login_time,109) as 'Servidor inicializado em ' from master..sysprocesses where spid = 1<br />
<br />
print 'Number of connections..: ' + convert(varchar(30),@@connections)<br />
print 'Language...............: ' + convert(varchar(30),@@language)<br />
print 'Language Id............: ' + convert(varchar(30),@@langid)<br />
print 'Lock Timeout...........: ' + convert(varchar(30),@@LOCK_TIMEOUT)<br />
print 'Maximum of connections.: ' + convert(varchar(30),@@MAX_CONNECTIONS)<br />
print 'Server Name............: ' + convert(varchar(30),@@SERVERNAME)<br />
print 'Instance...............: ' + convert(varchar(30),@@SERVICENAME)<br />
print ''<br />
print 'CPU Busy...........: ' + convert(varchar(30),@@CPU_BUSY/1000)<br />
print 'CPU Idle...........: ' + convert(varchar(30),@@IDLE/1000)<br />
print 'IO Busy............: ' + convert(varchar(30),@@IO_BUSY/1000)<br />
print 'Packets received...: ' + convert(varchar(30),@@PACK_RECEIVED)<br />
print 'Packets sent.......: ' + convert(varchar(30),@@PACK_SENT)<br />
print 'Packets w errors...: ' + convert(varchar(30),@@PACKET_ERRORS)<br />
print 'TimeTicks..........: ' + convert(varchar(30),@@TIMETICKS)<br />
print 'IO Errors..........: ' + convert(varchar(30),@@TOTAL_ERRORS)<br />
print 'Total Read.........: ' + convert(varchar(30),@@TOTAL_READ)<br />
print 'Total Write.........: ' + convert(varchar(30),@@TOTAL_WRITE)<br />
go<br />
<br />
----------------------------------------------------------------------------------------------------------<br />
print ''<br />
print '2. Server Parameters'<br />
print '*************************'<br />
print ''<br />
<br />
--exec sp_configure 'show advanced options',1<br />
exec sp_configure<br />
go<br />
----------------------------------------------------------------------------------------------------------<br />
print ''<br />
print '3. Databases parameters'<br />
print '***************************'<br />
print ''<br />
<br />
exec sp_helpdb<br />
go<br />
<br />
SELECT LEFT(name,30) AS DB,<br />
SUBSTRING(CASE status & 1 WHEN 0 THEN '' ELSE ',autoclose' END +<br />
CASE status & 4 WHEN 0 THEN '' ELSE ',select into/bulk copy' END +<br />
CASE status & 8 WHEN 0 THEN '' ELSE ',trunc. log on chkpt' END +<br />
CASE status & 16 WHEN 0 THEN '' ELSE ',torn page detection' END +<br />
CASE status & 32 WHEN 0 THEN '' ELSE ',loading' END +<br />
CASE status & 64 WHEN 0 THEN '' ELSE ',pre-recovery' END +<br />
CASE status & 128 WHEN 0 THEN '' ELSE ',recovering' END +<br />
CASE status & 256 WHEN 0 THEN '' ELSE ',not recovered' END +<br />
CASE status & 512 WHEN 0 THEN '' ELSE ',offline' END +<br />
CASE status & 1024 WHEN 0 THEN '' ELSE ',read only' END +<br />
CASE status & 2048 WHEN 0 THEN '' ELSE ',dbo USE only' END +<br />
CASE status & 4096 WHEN 0 THEN '' ELSE ',single user' END +<br />
CASE status & 32768 WHEN 0 THEN '' ELSE ',emergency mode' END +<br />
CASE status & 4194304 WHEN 0 THEN '' ELSE ',autoshrink' END +<br />
CASE status & 1073741824 WHEN 0 THEN '' ELSE ',cleanly shutdown' END +<br />
CASE status2 & 16384 WHEN 0 THEN '' ELSE ',ANSI NULL default' END +<br />
CASE status2 & 65536 WHEN 0 THEN '' ELSE ',concat NULL yields NULL' END +<br />
CASE status2 & 131072 WHEN 0 THEN '' ELSE ',recursive triggers' END +<br />
CASE status2 & 1048576 WHEN 0 THEN '' ELSE ',default TO local cursor' END +<br />
CASE status2 & 8388608 WHEN 0 THEN '' ELSE ',quoted identifier' END +<br />
CASE status2 & 33554432 WHEN 0 THEN '' ELSE ',cursor CLOSE on commit' END +<br />
CASE status2 & 67108864 WHEN 0 THEN '' ELSE ',ANSI NULLs' END +<br />
CASE status2 & 268435456 WHEN 0 THEN '' ELSE ',ANSI warnings' END +<br />
CASE status2 & 536870912 WHEN 0 THEN '' ELSE ',full text enabled' END,<br />
2,8000) AS Descr<br />
FROM master..sysdatabases<br />
go<br />
----------------------------------------------------------------------------------------------------------<br />
print ''<br />
print '4. LOG utilization'<br />
print '****************************'<br />
print ''<br />
<br />
dbcc sqlperf(logspace)<br />
go<br />
----------------------------------------------------------------------------------------------------------<br />
print ''<br />
print '5. Datafiles list'<br />
print '***********************'<br />
print ''<br />
<br />
if exists (select [id] from tempdb..sysobjects where [id] = OBJECT_ID ('tempdb..#TempForFileStats '))<br />
DROP TABLE #TempForFileStats<br />
<br />
if exists (select [id] from tempdb..sysobjects where [id] = OBJECT_ID ('tempdb..#TempForDataFile'))<br />
DROP TABLE #TempForDataFile<br />
<br />
if exists (select [id] from tempdb..sysobjects where [id] = OBJECT_ID ('tempdb..#TempForLogFile'))<br />
DROP TABLE #TempForLogFile<br />
<br />
DECLARE @DBName nvarchar(100)<br />
DECLARE @SQLString nvarchar (4000)<br />
DECLARE c_db CURSOR FOR<br />
SELECT name<br />
FROM master.dbo.sysdatabases<br />
WHERE status&512 = 0<br />
<br />
CREATE TABLE #TempForFileStats([Server Name] nvarchar(40),<br />
[Database Name] nvarchar(100),<br />
[File Name] nvarchar(128),<br />
[Usage Type] varchar (6),<br />
[Size (MB)] real,<br />
[Space Used (MB)] real,<br />
[MaxSize (MB)] real,<br />
[Next Allocation (MB)] real,<br />
[Growth Type] varchar (12),<br />
[File Id] smallint,<br />
[Group Id] smallint,<br />
[Physical File] nvarchar (260),<br />
[Date Checked] datetime)<br />
<br />
CREATE TABLE #TempForDataFile ([File Id] smallint,<br />
[Group Id] smallint,<br />
[Total Extents] int,<br />
[Used Extents] int,<br />
[File Name] nvarchar(128),<br />
[Physical File] nvarchar(260))<br />
<br />
CREATE TABLE #TempForLogFile ([File Id] int,<br />
[Size (Bytes)] real,<br />
[Start Offset] varchar(30),<br />
[FSeqNo] int,<br />
[Status] int,<br />
[Parity] smallint,<br />
[CreateTime] varchar(30))<br />
<br />
OPEN c_db<br />
FETCH NEXT FROM c_db INTO @DBName<br />
WHILE @@FETCH_STATUS = 0<br />
BEGIN<br />
SET @SQLString = 'SELECT @@SERVERNAME as ''ServerName'', ' + -- changed by seraj : *8 to *8.000000000<br />
'''' + @DBName + '''' + ' as ''Database'', ' +<br />
' f.name, ' +<br />
' CASE ' +<br />
' WHEN (64 & f.status) = 64 THEN ''Log'' ' +<br />
' ELSE ''Data'' ' +<br />
' END as ''Usage Type'', ' +<br />
' f.size*8.00000000/1024.00 as ''Size (MB)'', ' +<br />
' NULL as ''Space Used (MB)'', ' +<br />
' CASE f.maxsize ' +<br />
' WHEN -1 THEN -1 ' +<br />
' WHEN 0 THEN f.size*8.00000000/1024.00 ' +<br />
' ELSE f.maxsize*8.00000000/1024.00 ' +<br />
' END as ''Max Size (MB)'', ' +<br />
' CASE ' +<br />
' WHEN (1048576&f.status) = 1048576 THEN (growth/100.00)*(f.size*8.00000000/1024.00) ' +<br />
' WHEN f.growth =0 THEN 0 ' +<br />
' ELSE f.growth*8.00000000/1024.00 ' +<br />
' END as ''Next Allocation (MB)'', ' +<br />
' CASE ' +<br />
' WHEN (1048576&f.status) = 1048576 THEN ''Percentage'' ' +<br />
' ELSE ''Pages'' ' +<br />
' END as ''Usage Type'', ' +<br />
' f.fileid, ' +<br />
' f.groupid, ' +<br />
' filename, ' +<br />
' getdate() ' +<br />
' FROM [' + @DBName + '].dbo.sysfiles f' -- Seraj Alam added []<br />
INSERT #TempForFileStats<br />
EXECUTE(@SQLString)<br />
<br />
------------------------------------------------------------------------<br />
SET @SQLString = 'USE [' + @DBName + '] DBCC SHOWFILESTATS' -- seraj alam added []<br />
INSERT #TempForDataFile<br />
EXECUTE(@SQLString)<br />
--<br />
UPDATE #TempForFileStats<br />
SET [Space Used (MB)] = s.[Used Extents]*64/1024.00<br />
FROM #TempForFileStats f,<br />
#TempForDataFile s<br />
WHERE f.[File Id] = s.[File Id]<br />
AND f.[Group Id] = s.[Group Id]<br />
AND f.[Database Name] = @DBName<br />
--<br />
TRUNCATE TABLE #TempForDataFile<br />
-------------------------------------------------------------------------<br />
<br />
SET @SQLString = 'USE [' + @DBName + '] DBCC LOGINFO' -- seraj alam added []<br />
INSERT #TempForLogFile<br />
EXECUTE(@SQLString)<br />
--<br />
UPDATE #TempForFileStats<br />
SET [Space Used (MB)] = (SELECT (MIN(l.[Start Offset]) +<br />
SUM(CASE<br />
WHEN l.Status <> 0 THEN l.[Size (Bytes)]<br />
ELSE 0<br />
END))/1048576.00<br />
FROM #TempForLogFile l<br />
WHERE l.[File Id] = f.[File Id])<br />
FROM #TempForFileStats f<br />
WHERE f.[Database Name] = @DBName<br />
AND f.[Usage Type] = 'Log'<br />
--<br />
TRUNCATE TABLE #TempForLogFile<br />
-------------------------------------------------------------------------<br />
FETCH NEXT FROM c_db INTO @DBName<br />
END<br />
DEALLOCATE c_db<br />
<br />
SELECT * FROM #TempForFileStats<br />
------------<br />
DROP TABLE #TempForFileStats<br />
DROP TABLE #TempForDataFile<br />
DROP TABLE #TempForLogFile<br />
go<br />
----------------------------------------------------------------------------------------------------------<br />
print ''<br />
print '6. IO per datafile'<br />
print '******************'<br />
print ''<br />
use tempdb<br />
go<br />
<br />
if exists (select [id] from sysobjects where [id] = OBJECT_ID ('#TBL_DATABASEFILES'))<br />
DROP TABLE #TBL_DATABASEFILES<br />
<br />
<br />
if exists (select [id] from sysobjects where [id] = OBJECT_ID ('#TBL_FILESTATISTICS'))<br />
DROP TABLE #TBL_FILESTATISTICS<br />
<br />
<br />
DECLARE @INT_LOOPCOUNTER INTEGER<br />
DECLARE @INT_MAXCOUNTER INTEGER<br />
DECLARE @INT_DBID INTEGER<br />
DECLARE @INT_FILEID INTEGER<br />
DECLARE @SNM_DATABASENAME SYSNAME<br />
DECLARE @SNM_FILENAME SYSNAME<br />
DECLARE @NVC_EXECUTESTRING NVARCHAR(4000)<br />
<br />
DECLARE @MTB_DATABASES TABLE (<br />
ID INT IDENTITY,<br />
DBID INT,<br />
DBNAME SYSNAME )<br />
<br />
CREATE TABLE #TBL_DATABASEFILES (<br />
ID INT IDENTITY,<br />
DBID INT,<br />
FILEID INT,<br />
FILENAME SYSNAME,<br />
FILENAME1 varchar(600),<br />
DATABASENAME SYSNAME)<br />
<br />
INSERT INTO @MTB_DATABASES (DBID,DBNAME) SELECT DBID,NAME FROM MASTER.DBO.SYSDATABASES ORDER BY DBID<br />
SET @INT_LOOPCOUNTER = 1<br />
SELECT @INT_MAXCOUNTER=MAX(ID) FROM @MTB_DATABASES<br />
WHILE @INT_LOOPCOUNTER <= @INT_MAXCOUNTER<br />
BEGIN<br />
SELECT @INT_DBID = DBID,@SNM_DATABASENAME=DBNAME FROM @MTB_DATABASES WHERE ID = @INT_LOOPCOUNTER<br />
SET @NVC_EXECUTESTRING = 'INSERT INTO #TBL_DATABASEFILES(DBID,FILEID,FILENAME, FILENAME1,DATABASENAME) SELECT '+STR(@INT_DBID)+' AS DBID,FILEID,NAME AS FILENAME, FILENAME AS FILENAME1,'''+@SNM_DATABASENAME+''' AS DATABASENAME FROM ['+@SNM_DATABASENAME+'].DBO.SYSFILES'<br />
EXEC SP_EXECUTESQL @NVC_EXECUTESTRING<br />
SET @INT_LOOPCOUNTER = @INT_LOOPCOUNTER + 1<br />
END<br />
--'OK WE NOW HAVE ALL THE DATABASES AND FILENAMES ETC....<br />
<br />
CREATE TABLE #TBL_FILESTATISTICS (<br />
ID INT IDENTITY,<br />
DBID INT,<br />
FILEID INT,<br />
DATABASENAME SYSNAME,<br />
FILENAME SYSNAME,<br />
SAMPLETIME DATETIME,<br />
NUMBERREADS BIGINT,<br />
NUMBERWRITES BIGINT,<br />
BYTESREAD BIGINT,<br />
BYTESWRITTEN BIGINT,<br />
IOSTALLMS BIGINT)<br />
<br />
SELECT @INT_MAXCOUNTER=MAX(ID) FROM #TBL_DATABASEFILES<br />
SET @INT_LOOPCOUNTER = 1<br />
WHILE @INT_LOOPCOUNTER <= @INT_MAXCOUNTER<br />
BEGIN<br />
SELECT @INT_DBID = DBID,@INT_FILEID=FILEID,@SNM_DATABASENAME=DATABASENAME,@SNM_FILENAME=FILENAME FROM #TBL_DATABASEFILES WHERE ID = @INT_LOOPCOUNTER<br />
INSERT INTO #TBL_FILESTATISTICS(DBID,FILEID,SAMPLETIME,NUMBERREADS,NUMBERWRITES,BYTESREAD,BYTESWRITTEN,IOSTALLMS,DATABASENAME,FILENAME)<br />
SELECT DBID,FILEID,GETDATE(),NUMBERREADS,NUMBERWRITES,BYTESREAD,BYTESWRITTEN,IOSTALLMS,@SNM_DATABASENAME AS DATABASENAME,@SNM_FILENAME AS FILENAME FROM :: FN_VIRTUALFILESTATS(@INT_DBID,@INT_FILEID)<br />
SET @INT_LOOPCOUNTER = @INT_LOOPCOUNTER + 1<br />
END<br />
select * from #TBL_FILESTATISTICS<br />
<br />
drop table #TBL_DATABASEFILES<br />
drop table #TBL_FILESTATISTICS<br />
go<br />
---------------------------------------------------------------------------------------<br />
print ''<br />
print '7. List of last backup full''s'<br />
print '*************************************'<br />
print ''<br />
<br />
select <span class="Apple-tab-span" style="white-space: pre;"> </span>SUBSTRING(s.name,1,40)<span class="Apple-tab-span" style="white-space: pre;"> </span>AS<span class="Apple-tab-span" style="white-space: pre;"> </span>'Database',<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>CAST(b.backup_start_date AS char(11)) <span class="Apple-tab-span" style="white-space: pre;"> </span>AS <span class="Apple-tab-span" style="white-space: pre;"> </span>'Backup Date ',<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>CASE WHEN b.backup_start_date > DATEADD(dd,-1,getdate())<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>THEN 'Backup is current within a day'<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span> WHEN b.backup_start_date > DATEADD(dd,-7,getdate())<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>THEN 'Backup is current within a week'<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span> ELSE '*****CHECK BACKUP!!!*****'<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>END<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>AS 'Comment'<br />
<br />
from <span class="Apple-tab-span" style="white-space: pre;"> </span>master..sysdatabases<span class="Apple-tab-span" style="white-space: pre;"> </span>s<br />
LEFT OUTER JOIN<span class="Apple-tab-span" style="white-space: pre;"> </span>msdb..backupset b<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>ON s.name = b.database_name<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>AND b.backup_start_date = (SELECT MAX(backup_start_date)<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>FROM msdb..backupset<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>WHERE database_name = b.database_name<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>AND type = 'D')<span class="Apple-tab-span" style="white-space: pre;"> </span>-- full database backups only, not log backups<br />
WHERE<span class="Apple-tab-span" style="white-space: pre;"> </span>s.name <> 'tempdb'<br />
<br />
ORDER BY <span class="Apple-tab-span" style="white-space: pre;"> </span>s.name<br />
go<br />
----------------------------------------------------------------------------------------------------------<br />
print ''<br />
print '8. List of logins'<br />
print '********************'<br />
print ''<br />
<br />
exec sp_helplogins<br />
go<br />
----------------------------------------------------------------------------------------------------------<br />
print ''<br />
print '9. List of users per role'<br />
print '*******************************'<br />
print ''<br />
<br />
exec sp_helpsrvrolemember<br />
go<br />
----------------------------------------------------------------------------------------------------------<br />
print ''<br />
print '10.List of special users per database'<br />
print '*************************************'<br />
print ''<br />
<br />
<br />
declare @name sysname,<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>@SQL nvarchar(600)<br />
<br />
if exists (select [id] from tempdb..sysobjects where [id] = OBJECT_ID ('tempdb..#tmpTable'))<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>drop table #tmpTable<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span><br />
CREATE TABLE #tmpTable (<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>[DATABASE_NAME] sysname NOT NULL ,<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>[USER_NAME] sysname NOT NULL,<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>[ROLE_NAME] sysname NOT NULL)<br />
<br />
declare c1 cursor for<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>select name from master.dbo.sysdatabases<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span><br />
open c1<br />
fetch c1 into @name<br />
while @@fetch_status >= 0<br />
begin<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>select @SQL =<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>'insert into #tmpTable<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span> select N'''+ @name + ''', a.name, c.name<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>from ' + QuoteName(@name) + '.dbo.sysusers a<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>join ' + QuoteName(@name) + '.dbo.sysmembers b on b.memberuid = a.uid<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>join ' + QuoteName(@name) + '.dbo.sysusers c on c.uid = b.groupuid<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>where a.name != ''dbo'''<br />
<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>/* <span class="Apple-tab-span" style="white-space: pre;"> </span>Insert row for each database */<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>execute (@SQL)<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>fetch c1 into @name<br />
end<br />
close c1<br />
deallocate c1<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span><br />
select * from #tmpTable<br />
<br />
drop table #tmpTable<br />
go<br />
----------------------------------------------------------------------------------------------------------<br />
print ''<br />
print '11. Information about remote servers '<br />
print '*****************************************'<br />
print ''<br />
<br />
Print 'Linked Servers'<br />
print ''<br />
<br />
exec sp_linkedservers<br />
<br />
print 'linked Server login mappings'<br />
print ''<br />
<br />
exec sp_helplinkedsrvlogin<br />
<br />
print 'Remote Logins'<br />
print ''<br />
<br />
--exec sp_helpremotelogin<br />
<br />
go<br />
----------------------------------------------------------------------------------------------------------<br />
print ''<br />
print '12. List of jobs '<br />
print '*******************'<br />
print ''<br />
<br />
exec msdb..sp_help_job<br />
go<br />
----------------------------------------------------------------------------------------------------------<br />
<br />
print ''<br />
print '13. Cache Hit Ratio '<br />
print '*******************'<br />
print ''<br />
<br />
select <span class="Apple-tab-span" style="white-space: pre;"> </span>distinct counter_name,<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>(select isnull(sum(convert(dec(15,0),B.cntr_value)),0)<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>from <span class="Apple-tab-span" style="white-space: pre;"> </span>master..sysperfinfo as B (nolock)<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>where <span class="Apple-tab-span" style="white-space: pre;"> </span>Lower(B.counter_name) like '%hit ratio%'<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>and<span class="Apple-tab-span" style="white-space: pre;"> </span>A.counter_name = B.counter_name) as CurrHit,<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>(select isnull(sum(convert(dec(15,0),B.cntr_value)),0)<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>from <span class="Apple-tab-span" style="white-space: pre;"> </span>master..sysperfinfo as B (nolock)<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>where <span class="Apple-tab-span" style="white-space: pre;"> </span>Lower(B.counter_name) like '%hit ratio base%'<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>and<span class="Apple-tab-span" style="white-space: pre;"> </span>lower(B.counter_name) = (lower(ltrim(rtrim(A.counter_name))) + ' base')) as CurrBase,<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>(select isnull(sum(convert(dec(15,0),B.cntr_value)),0)<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>from <span class="Apple-tab-span" style="white-space: pre;"> </span>master..sysperfinfo as B (nolock)<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>where <span class="Apple-tab-span" style="white-space: pre;"> </span>Lower(B.counter_name) like '%hit ratio%'<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>and<span class="Apple-tab-span" style="white-space: pre;"> </span>A.counter_name = B.counter_name) /<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>(select isnull(sum(convert(dec(15,0),B.cntr_value)),0)<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>from <span class="Apple-tab-span" style="white-space: pre;"> </span>master..sysperfinfo as B (nolock)<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>where <span class="Apple-tab-span" style="white-space: pre;"> </span>Lower(B.counter_name) like '%hit ratio base%'<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>and<span class="Apple-tab-span" style="white-space: pre;"> </span>lower(B.counter_name) = (lower(ltrim(rtrim(A.counter_name))) + ' base')) as HitRatio<br />
from <span class="Apple-tab-span" style="white-space: pre;"> </span>master..sysperfinfo as A (nolock)<br />
where <span class="Apple-tab-span" style="white-space: pre;"> </span>Lower(A.counter_name) like '%hit ratio%'<br />
and <span class="Apple-tab-span" style="white-space: pre;"> </span>Lower(A.counter_name) not like '%hit ratio base%'<br />
<br />
-- Audit list as a double verification<br />
<br />
select counter_name,isnull(sum(convert(dec(15,0),cntr_value)),0) as Value<br />
from <span class="Apple-tab-span" style="white-space: pre;"> </span>master..sysperfinfo (nolock)<br />
where <span class="Apple-tab-span" style="white-space: pre;"> </span>Lower(counter_name) like '%hit ratio%'<br />
or <span class="Apple-tab-span" style="white-space: pre;"> </span>Lower(counter_name) like '%hit ratio base%'<br />
group by counter_name<br />
<br />
go<br />
----------------------------------------------------------------------------------------------------------<br />
<br />
print ''<br />
print '14. SP_WHO '<br />
print '***********'<br />
print ''<br />
exec sp_who<br />
exec sp_who2<br />
go<br />
<br />
----------------------------------------------------------------------------------------------------------<br />
<br />
print ''<br />
print '14. SP_LOCKS '<br />
print '***********'<br />
print ''<br />
exec sp_lock<br />
<br />
go<br />
<br />
<br />
set nocount on<br />
use msdb<br />
go<br />
Print ''<br />
Print 'Linked Servers'<br />
print '***********'<br />
print ''<br />
<br />
exec sp_linkedservers<br />
<br />
Print ''<br />
print '15. Maintenance Plans'<br />
print '***********'<br />
print ''<br />
go<br />
<br />
--select @@servername "ServerName", smp.plan_id, plan_name, owner, smpd.database_name, smpj.job_id, sj.name, sjs.name "Job Part Name", sjs.enabled "Job Enabled", sjs.freq_type "Frequency", sjs.active_start_time "Job Start Time"<br />
--from sysdbmaintplans smp, sysdbmaintplan_databases smpd, sysdbmaintplan_jobs smpj, sysjobs sj, sysjobschedules sjs<br />
--where smp.plan_id = smpd.plan_id and smp.plan_id = smpj.plan_id and sj.job_id = smpj.job_id and sj.job_id = sjs.job_id<br />
go<br />
Print ''<br />
print 'Maintenance Plan 2005'<br />
SELECT sv.name AS [Name], sv.job_id AS [JobID],sv.originating_server AS [OriginatingServer],<br />
CAST(sv.enabled AS bit) AS [IsEnabled],<br />
ISNULL(sv.description,N'') AS [Description],sv.start_step_id AS [StartStepID],ISNULL(suser_sname(sv.owner_sid), N'') AS [OwnerLoginName],<br />
sv.notify_level_eventlog AS [EventLogLevel],sv.notify_level_email AS [EmailLevel],sv.notify_level_netsend AS [NetSendLevel],<br />
sv.notify_level_page AS [PageLevel],sv.delete_level AS [DeleteLevel],sv.date_created AS [DateCreated],sv.date_modified AS [DateLastModified],<br />
sv.version_number AS [VersionNumber]FROM msdb.dbo.sysjobs_view AS sv<br />
go<br />
<br />
<br />
set dateformat dmy<br />
<br />
use msdb<br />
go<br />
<br />
select y.Server_name, y.database_name, y.database_creation_date, y.backup_start_Date, y.backup_finish_Date , y.backup_file, y.backup_size<br />
from (<br />
select c.Server_name, c.database_name, c.database_creation_date, c.backup_start_Date, c.backup_finish_Date , a.physical_device_name as backup_file, c.backup_size<br />
from Backupmediafamily a inner join backupset c on a.media_set_id=c.media_set_id<br />
inner join backupfile b on<br />
c.backup_set_id=b.backup_set_id and b.file_type='D'<br />
) y<br />
inner join<br />
(select distinct c.database_name, c.backup_finish_Date as backup_finish_date from Backupmediafamily a inner join backupset c on a.media_set_id=c.media_set_id<br />
inner join backupfile b on<br />
c.backup_set_id=b.backup_set_id and b.file_type='D' where c.backup_finish_date > getdate()-10<br />
) z<br />
on y.database_name=z.database_name and y.backup_finish_Date=z.backup_finish_date<br />
<br />
<br />
<br />
<br />
<br />
Print ''<br />
Print '16. OTHER'<br />
PRINT 'DTS Packages'<br />
print '***********'<br />
print ''<br />
<br />
go<br />
select @@servername "ServerName", sd.name, sd.id, sd.categoryid, sdc.name "Category", sd.description, owner, createdate<br />
from sysdtspackages sd, sysdtscategories sdc<br />
where sd.categoryid = sdc.id<br />
go<br />
exec sp_MSgetalertinfo<br />
<br />
print ''<br />
print 'Alerts settings'<br />
print '***********'<br />
print ''<br />
<br />
exec msdb..sp_help_alert<br />
<br />
Print ''<br />
Print 'Operators'<br />
print '***********'<br />
print ''<br />
<br />
EXECUTE sp_help_operator<br />
<br />
print ''<br />
print 'SQL Mails'<br />
EXECUTE master.dbo.xp_sqlagent_notify N'M',null,null,null,N'E'<br />
<br />
print ''<br />
Print 'SQL Agent Propertes'<br />
EXECUTE msdb.dbo.sp_get_sqlagent_properties<br />
<br />
print ''<br />
print 'Startup Parameters 01'<br />
exec master..xp_instance_regread N'HKEY_LOCAL_MACHINE',<br />
N'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer\Parameters', N'SQLarg0'<br />
<br />
print ''<br />
print 'Startup Parameters 02'<br />
exec master..xp_instance_regread N'HKEY_LOCAL_MACHINE',<br />
N'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer\Parameters', N'SQLarg1'<br />
<br />
print ''<br />
print 'Startup Parameters 03'<br />
exec master..xp_instance_regread N'HKEY_LOCAL_MACHINE',<br />
N'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer\Parameters', N'SQLarg2'<br />
<br />
print ''<br />
print 'Startup Parameters 04'<br />
exec master..xp_instance_regread N'HKEY_LOCAL_MACHINE',<br />
N'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer\Parameters', N'SQLarg3'<br />
<br />
-- Though there is very little chance of more than 4 parameters, still it is good to check.<br />
<br />
print ''<br />
print 'Startup Parameters 05'<br />
exec master..xp_instance_regread N'HKEY_LOCAL_MACHINE',<br />
N'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer\Parameters', N'SQLarg4'<br />
<br />
print ''<br />
print 'Startup Parameters 06'<br />
exec master..xp_instance_regread N'HKEY_LOCAL_MACHINE',<br />
N'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer\Parameters', N'SQLarg5'<br />
<br />
print ''<br />
<br />
--exec master..xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\Setup', N'SQLPath'<br />
<br />
<br />
print ''<br />
print 'Login Mode'<br />
exec master..xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer', 'LoginMode'<br />
go<br />
<br />
print ''<br />
Print 'Audit Level'<br />
<br />
exec master..xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer', N'AuditLevel'<br />
<br />
Print ''<br />
Print 'Clustered?'<br />
select convert(int, serverproperty(N'isclustered'))<br />
go<br />
Print ''<br />
Print 'Is Mapi set?'<br />
DECLARE @retval varchar(255) EXECUTE master.dbo.xp_instance_regread 'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft\Windows Messaging Subsystem','MAPIX',@retval OUTPUT SELECT @retval<br />
go<br />
<br />
Print ''<br />
Print 'Mail Account'<br />
exec master..xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer', N'MailAccountName'<br />
Print ''<br />
<br />
set nocount off<br />
<br />
<br />
print '******************************************************************'<br />
print ' FIM '<br />
print '******************************************************************'<br />
----------------------------------------------------------------------------------------------------------<br />
set nocount off<br />
<br />
print ''<br />
print 'disabled logins'<br />
use master<br />
go<br />
select name,type,is_disabled,create_date from sys.server_principals where is_disabled = '1'<br />
go<br />
-------------------------------------------------------------------------------------------------------<br />
print ''<br />
print 'deny logins'<br />
use master<br />
go<br />
select name,loginname,denylogin,createdate,hasaccess from sys.syslogins where denylogin ='1'<br />
<br />
go<br />
<br />
-----------------------------------------------------------------------------------------------------<br />
print '********************************************'<br />
print ''<br />
print ' SQL server Ip and Port'<br />
use master<br />
<br />
select distinct local_net_address,local_tcp_port from sys.dm_exec_connections<br />
where client_net_address != '<local machine>'<br />
<br />
go<br />
<br />
----------------------------------------------------------------------------------------------------------------<br />
</div>
Anonymoushttp://www.blogger.com/profile/01716610258406540066noreply@blogger.com0tag:blogger.com,1999:blog-8576109261886596415.post-8725518227753615862012-10-02T00:35:00.002+05:302012-10-02T00:35:28.970+05:30Script to find all the details of a SQL Server 2000<div dir="ltr" style="text-align: left;" trbidi="on">
The below script will provide all the details of the SQL Server 2000. This script can be very useful when you are taking the server in support and you want to keep a record of the server details at the time of possession.<br />
<br />
<br />
<br />
--------------------------------------------------------<br />
<br />
SET NOCOUNT ON<br />
use master<br />
go<br />
<br />
print '***************************************************************'<br />
print ' MANUAL ACTIVITIES '<br />
print ' '<br />
print ' A. See database startup parameters '<br />
print ' B. See SQL Server Error Log and NT Event Viewer '<br />
print ' C. See authentication mode ( NATIVE or MIXED ) '<br />
print ' D. See SQL Server and SQL Agent services account startup '<br />
print ' E. See SQL Mail configuration <span class="Apple-tab-span" style="white-space: pre;"> </span> '<br />
print ' F. See backup politic ( full and transaction ) '<br />
print '***************************************************************'<br />
<br />
print ''<br />
print '1. General Info'<br />
print '*********************'<br />
print ''<br />
<br />
print 'Server Name...............: ' + convert(varchar(30),@@SERVERNAME)<br />
print 'Instance..................: ' + convert(varchar(30),@@SERVICENAME)<br />
print 'Current Date Time.........: ' + convert(varchar(30),getdate(),113)<br />
print 'User......................: ' + USER_NAME()<br />
go<br />
<br />
print ''<br />
print '1.1 Database and Operational System versions.'<br />
print '----------------------------------------------'<br />
print ''<br />
<br />
select @@version<br />
go<br />
<br />
exec master..xp_msver<br />
go<br />
<br />
print ''<br />
print'*****************************'<br />
<br />
SELECT createdate as Sql_Server_Install_Date<br />
FROM syslogins<br />
where name = 'NT AUTHORITY\SYSTEM'<br />
<br />
print ''<br />
print '1.2 Miscelaneous'<br />
print '---------------------------'<br />
print ''<br />
<br />
select convert(varchar(30),login_time,109) as 'Servidor inicializado em ' from master..sysprocesses where spid = 1<br />
<br />
print 'Number of connections..: ' + convert(varchar(30),@@connections)<br />
print 'Language...............: ' + convert(varchar(30),@@language)<br />
print 'Language Id............: ' + convert(varchar(30),@@langid)<br />
print 'Lock Timeout...........: ' + convert(varchar(30),@@LOCK_TIMEOUT)<br />
print 'Maximum of connections.: ' + convert(varchar(30),@@MAX_CONNECTIONS)<br />
print 'Server Name............: ' + convert(varchar(30),@@SERVERNAME)<br />
print 'Instance...............: ' + convert(varchar(30),@@SERVICENAME)<br />
print ''<br />
print 'CPU Busy...........: ' + convert(varchar(30),@@CPU_BUSY/1000)<br />
print 'CPU Idle...........: ' + convert(varchar(30),@@IDLE/1000)<br />
print 'IO Busy............: ' + convert(varchar(30),@@IO_BUSY/1000)<br />
print 'Packets received...: ' + convert(varchar(30),@@PACK_RECEIVED)<br />
print 'Packets sent.......: ' + convert(varchar(30),@@PACK_SENT)<br />
print 'Packets w errors...: ' + convert(varchar(30),@@PACKET_ERRORS)<br />
print 'TimeTicks..........: ' + convert(varchar(30),@@TIMETICKS)<br />
print 'IO Errors..........: ' + convert(varchar(30),@@TOTAL_ERRORS)<br />
print 'Total Read.........: ' + convert(varchar(30),@@TOTAL_READ)<br />
print 'Total Write.........: ' + convert(varchar(30),@@TOTAL_WRITE)<br />
go<br />
<br />
----------------------------------------------------------------------------------------------------------<br />
print ''<br />
print '2. Server Parameters'<br />
print '*************************'<br />
print ''<br />
<br />
--exec sp_configure 'show advanced options',1<br />
exec sp_configure<br />
go<br />
----------------------------------------------------------------------------------------------------------<br />
print ''<br />
print '3. Databases parameters'<br />
print '***************************'<br />
print ''<br />
<br />
exec sp_helpdb<br />
go<br />
<br />
SELECT LEFT(name,30) AS DB,<br />
SUBSTRING(CASE status & 1 WHEN 0 THEN '' ELSE ',autoclose' END +<br />
CASE status & 4 WHEN 0 THEN '' ELSE ',select into/bulk copy' END +<br />
CASE status & 8 WHEN 0 THEN '' ELSE ',trunc. log on chkpt' END +<br />
CASE status & 16 WHEN 0 THEN '' ELSE ',torn page detection' END +<br />
CASE status & 32 WHEN 0 THEN '' ELSE ',loading' END +<br />
CASE status & 64 WHEN 0 THEN '' ELSE ',pre-recovery' END +<br />
CASE status & 128 WHEN 0 THEN '' ELSE ',recovering' END +<br />
CASE status & 256 WHEN 0 THEN '' ELSE ',not recovered' END +<br />
CASE status & 512 WHEN 0 THEN '' ELSE ',offline' END +<br />
CASE status & 1024 WHEN 0 THEN '' ELSE ',read only' END +<br />
CASE status & 2048 WHEN 0 THEN '' ELSE ',dbo USE only' END +<br />
CASE status & 4096 WHEN 0 THEN '' ELSE ',single user' END +<br />
CASE status & 32768 WHEN 0 THEN '' ELSE ',emergency mode' END +<br />
CASE status & 4194304 WHEN 0 THEN '' ELSE ',autoshrink' END +<br />
CASE status & 1073741824 WHEN 0 THEN '' ELSE ',cleanly shutdown' END +<br />
CASE status2 & 16384 WHEN 0 THEN '' ELSE ',ANSI NULL default' END +<br />
CASE status2 & 65536 WHEN 0 THEN '' ELSE ',concat NULL yields NULL' END +<br />
CASE status2 & 131072 WHEN 0 THEN '' ELSE ',recursive triggers' END +<br />
CASE status2 & 1048576 WHEN 0 THEN '' ELSE ',default TO local cursor' END +<br />
CASE status2 & 8388608 WHEN 0 THEN '' ELSE ',quoted identifier' END +<br />
CASE status2 & 33554432 WHEN 0 THEN '' ELSE ',cursor CLOSE on commit' END +<br />
CASE status2 & 67108864 WHEN 0 THEN '' ELSE ',ANSI NULLs' END +<br />
CASE status2 & 268435456 WHEN 0 THEN '' ELSE ',ANSI warnings' END +<br />
CASE status2 & 536870912 WHEN 0 THEN '' ELSE ',full text enabled' END,<br />
2,8000) AS Descr<br />
FROM master..sysdatabases<br />
go<br />
----------------------------------------------------------------------------------------------------------<br />
print ''<br />
print '4. LOG utilization'<br />
print '****************************'<br />
print ''<br />
<br />
dbcc sqlperf(logspace)<br />
go<br />
----------------------------------------------------------------------------------------------------------<br />
print ''<br />
print '5. Datafiles list'<br />
print '***********************'<br />
print ''<br />
<br />
if exists (select [id] from tempdb..sysobjects where [id] = OBJECT_ID ('tempdb..#TempForFileStats '))<br />
DROP TABLE #TempForFileStats<br />
<br />
if exists (select [id] from tempdb..sysobjects where [id] = OBJECT_ID ('tempdb..#TempForDataFile'))<br />
DROP TABLE #TempForDataFile<br />
<br />
if exists (select [id] from tempdb..sysobjects where [id] = OBJECT_ID ('tempdb..#TempForLogFile'))<br />
DROP TABLE #TempForLogFile<br />
<br />
DECLARE @DBName nvarchar(100)<br />
DECLARE @SQLString nvarchar (4000)<br />
DECLARE c_db CURSOR FOR<br />
SELECT name<br />
FROM master.dbo.sysdatabases<br />
WHERE status&512 = 0<br />
<br />
CREATE TABLE #TempForFileStats([Server Name] nvarchar(40),<br />
[Database Name] nvarchar(100),<br />
[File Name] nvarchar(128),<br />
[Usage Type] varchar (6),<br />
[Size (MB)] real,<br />
[Space Used (MB)] real,<br />
[MaxSize (MB)] real,<br />
[Next Allocation (MB)] real,<br />
[Growth Type] varchar (12),<br />
[File Id] smallint,<br />
[Group Id] smallint,<br />
[Physical File] nvarchar (260),<br />
[Date Checked] datetime)<br />
<br />
CREATE TABLE #TempForDataFile ([File Id] smallint,<br />
[Group Id] smallint,<br />
[Total Extents] int,<br />
[Used Extents] int,<br />
[File Name] nvarchar(128),<br />
[Physical File] nvarchar(260))<br />
<br />
CREATE TABLE #TempForLogFile ([File Id] int,<br />
[Size (Bytes)] real,<br />
[Start Offset] varchar(30),<br />
[FSeqNo] int,<br />
[Status] int,<br />
[Parity] smallint,<br />
[CreateTime] varchar(30))<br />
<br />
OPEN c_db<br />
FETCH NEXT FROM c_db INTO @DBName<br />
WHILE @@FETCH_STATUS = 0<br />
BEGIN<br />
SET @SQLString = 'SELECT @@SERVERNAME as ''ServerName'', ' + -- changed by seraj : *8 to *8.000000000<br />
'''' + @DBName + '''' + ' as ''Database'', ' +<br />
' f.name, ' +<br />
' CASE ' +<br />
' WHEN (64 & f.status) = 64 THEN ''Log'' ' +<br />
' ELSE ''Data'' ' +<br />
' END as ''Usage Type'', ' +<br />
' f.size*8.00000000/1024.00 as ''Size (MB)'', ' +<br />
' NULL as ''Space Used (MB)'', ' +<br />
' CASE f.maxsize ' +<br />
' WHEN -1 THEN -1 ' +<br />
' WHEN 0 THEN f.size*8.00000000/1024.00 ' +<br />
' ELSE f.maxsize*8.00000000/1024.00 ' +<br />
' END as ''Max Size (MB)'', ' +<br />
' CASE ' +<br />
' WHEN (1048576&f.status) = 1048576 THEN (growth/100.00)*(f.size*8.00000000/1024.00) ' +<br />
' WHEN f.growth =0 THEN 0 ' +<br />
' ELSE f.growth*8.00000000/1024.00 ' +<br />
' END as ''Next Allocation (MB)'', ' +<br />
' CASE ' +<br />
' WHEN (1048576&f.status) = 1048576 THEN ''Percentage'' ' +<br />
' ELSE ''Pages'' ' +<br />
' END as ''Usage Type'', ' +<br />
' f.fileid, ' +<br />
' f.groupid, ' +<br />
' filename, ' +<br />
' getdate() ' +<br />
' FROM [' + @DBName + '].dbo.sysfiles f' -- Seraj Alam added []<br />
INSERT #TempForFileStats<br />
EXECUTE(@SQLString)<br />
<br />
------------------------------------------------------------------------<br />
SET @SQLString = 'USE [' + @DBName + '] DBCC SHOWFILESTATS' -- seraj alam added []<br />
INSERT #TempForDataFile<br />
EXECUTE(@SQLString)<br />
--<br />
UPDATE #TempForFileStats<br />
SET [Space Used (MB)] = s.[Used Extents]*64/1024.00<br />
FROM #TempForFileStats f,<br />
#TempForDataFile s<br />
WHERE f.[File Id] = s.[File Id]<br />
AND f.[Group Id] = s.[Group Id]<br />
AND f.[Database Name] = @DBName<br />
--<br />
TRUNCATE TABLE #TempForDataFile<br />
-------------------------------------------------------------------------<br />
<br />
SET @SQLString = 'USE [' + @DBName + '] DBCC LOGINFO' -- seraj alam added []<br />
INSERT #TempForLogFile<br />
EXECUTE(@SQLString)<br />
--<br />
UPDATE #TempForFileStats<br />
SET [Space Used (MB)] = (SELECT (MIN(l.[Start Offset]) +<br />
SUM(CASE<br />
WHEN l.Status <> 0 THEN l.[Size (Bytes)]<br />
ELSE 0<br />
END))/1048576.00<br />
FROM #TempForLogFile l<br />
WHERE l.[File Id] = f.[File Id])<br />
FROM #TempForFileStats f<br />
WHERE f.[Database Name] = @DBName<br />
AND f.[Usage Type] = 'Log'<br />
--<br />
TRUNCATE TABLE #TempForLogFile<br />
-------------------------------------------------------------------------<br />
FETCH NEXT FROM c_db INTO @DBName<br />
END<br />
DEALLOCATE c_db<br />
<br />
SELECT * FROM #TempForFileStats<br />
------------<br />
DROP TABLE #TempForFileStats<br />
DROP TABLE #TempForDataFile<br />
DROP TABLE #TempForLogFile<br />
go<br />
----------------------------------------------------------------------------------------------------------<br />
print ''<br />
print '6. IO per datafile'<br />
print '******************'<br />
print ''<br />
use tempdb<br />
go<br />
<br />
if exists (select [id] from sysobjects where [id] = OBJECT_ID ('#TBL_DATABASEFILES'))<br />
DROP TABLE #TBL_DATABASEFILES<br />
<br />
<br />
if exists (select [id] from sysobjects where [id] = OBJECT_ID ('#TBL_FILESTATISTICS'))<br />
DROP TABLE #TBL_FILESTATISTICS<br />
<br />
<br />
DECLARE @INT_LOOPCOUNTER INTEGER<br />
DECLARE @INT_MAXCOUNTER INTEGER<br />
DECLARE @INT_DBID INTEGER<br />
DECLARE @INT_FILEID INTEGER<br />
DECLARE @SNM_DATABASENAME SYSNAME<br />
DECLARE @SNM_FILENAME SYSNAME<br />
DECLARE @NVC_EXECUTESTRING NVARCHAR(4000)<br />
<br />
DECLARE @MTB_DATABASES TABLE (<br />
ID INT IDENTITY,<br />
DBID INT,<br />
DBNAME SYSNAME )<br />
<br />
CREATE TABLE #TBL_DATABASEFILES (<br />
ID INT IDENTITY,<br />
DBID INT,<br />
FILEID INT,<br />
FILENAME SYSNAME,<br />
FILENAME1 varchar(600),<br />
DATABASENAME SYSNAME)<br />
<br />
INSERT INTO @MTB_DATABASES (DBID,DBNAME) SELECT DBID,NAME FROM MASTER.DBO.SYSDATABASES ORDER BY DBID<br />
SET @INT_LOOPCOUNTER = 1<br />
SELECT @INT_MAXCOUNTER=MAX(ID) FROM @MTB_DATABASES<br />
WHILE @INT_LOOPCOUNTER <= @INT_MAXCOUNTER<br />
BEGIN<br />
SELECT @INT_DBID = DBID,@SNM_DATABASENAME=DBNAME FROM @MTB_DATABASES WHERE ID = @INT_LOOPCOUNTER<br />
SET @NVC_EXECUTESTRING = 'INSERT INTO #TBL_DATABASEFILES(DBID,FILEID,FILENAME, FILENAME1,DATABASENAME) SELECT '+STR(@INT_DBID)+' AS DBID,FILEID,NAME AS FILENAME, FILENAME AS FILENAME1,'''+@SNM_DATABASENAME+''' AS DATABASENAME FROM ['+@SNM_DATABASENAME+'].DBO.SYSFILES'<br />
EXEC SP_EXECUTESQL @NVC_EXECUTESTRING<br />
SET @INT_LOOPCOUNTER = @INT_LOOPCOUNTER + 1<br />
END<br />
--'OK WE NOW HAVE ALL THE DATABASES AND FILENAMES ETC....<br />
<br />
CREATE TABLE #TBL_FILESTATISTICS (<br />
ID INT IDENTITY,<br />
DBID INT,<br />
FILEID INT,<br />
DATABASENAME SYSNAME,<br />
FILENAME SYSNAME,<br />
SAMPLETIME DATETIME,<br />
NUMBERREADS BIGINT,<br />
NUMBERWRITES BIGINT,<br />
BYTESREAD BIGINT,<br />
BYTESWRITTEN BIGINT,<br />
IOSTALLMS BIGINT)<br />
<br />
SELECT @INT_MAXCOUNTER=MAX(ID) FROM #TBL_DATABASEFILES<br />
SET @INT_LOOPCOUNTER = 1<br />
WHILE @INT_LOOPCOUNTER <= @INT_MAXCOUNTER<br />
BEGIN<br />
SELECT @INT_DBID = DBID,@INT_FILEID=FILEID,@SNM_DATABASENAME=DATABASENAME,@SNM_FILENAME=FILENAME FROM #TBL_DATABASEFILES WHERE ID = @INT_LOOPCOUNTER<br />
INSERT INTO #TBL_FILESTATISTICS(DBID,FILEID,SAMPLETIME,NUMBERREADS,NUMBERWRITES,BYTESREAD,BYTESWRITTEN,IOSTALLMS,DATABASENAME,FILENAME)<br />
SELECT DBID,FILEID,GETDATE(),NUMBERREADS,NUMBERWRITES,BYTESREAD,BYTESWRITTEN,IOSTALLMS,@SNM_DATABASENAME AS DATABASENAME,@SNM_FILENAME AS FILENAME FROM :: FN_VIRTUALFILESTATS(@INT_DBID,@INT_FILEID)<br />
SET @INT_LOOPCOUNTER = @INT_LOOPCOUNTER + 1<br />
END<br />
select * from #TBL_FILESTATISTICS<br />
<br />
drop table #TBL_DATABASEFILES<br />
drop table #TBL_FILESTATISTICS<br />
go<br />
---------------------------------------------------------------------------------------<br />
print ''<br />
print '7. List of last backup full''s'<br />
print '*************************************'<br />
print ''<br />
<br />
select <span class="Apple-tab-span" style="white-space: pre;"> </span>SUBSTRING(s.name,1,40)<span class="Apple-tab-span" style="white-space: pre;"> </span>AS<span class="Apple-tab-span" style="white-space: pre;"> </span>'Database',<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>CAST(b.backup_start_date AS char(11)) <span class="Apple-tab-span" style="white-space: pre;"> </span>AS <span class="Apple-tab-span" style="white-space: pre;"> </span>'Backup Date ',<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>CASE WHEN b.backup_start_date > DATEADD(dd,-1,getdate())<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>THEN 'Backup is current within a day'<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span> WHEN b.backup_start_date > DATEADD(dd,-7,getdate())<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>THEN 'Backup is current within a week'<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span> ELSE '*****CHECK BACKUP!!!*****'<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>END<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>AS 'Comment'<br />
<br />
from <span class="Apple-tab-span" style="white-space: pre;"> </span>master..sysdatabases<span class="Apple-tab-span" style="white-space: pre;"> </span>s<br />
LEFT OUTER JOIN<span class="Apple-tab-span" style="white-space: pre;"> </span>msdb..backupset b<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>ON s.name = b.database_name<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>AND b.backup_start_date = (SELECT MAX(backup_start_date)<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>FROM msdb..backupset<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>WHERE database_name = b.database_name<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>AND type = 'D')<span class="Apple-tab-span" style="white-space: pre;"> </span>-- full database backups only, not log backups<br />
WHERE<span class="Apple-tab-span" style="white-space: pre;"> </span>s.name <> 'tempdb'<br />
<br />
ORDER BY <span class="Apple-tab-span" style="white-space: pre;"> </span>s.name<br />
go<br />
----------------------------------------------------------------------------------------------------------<br />
print ''<br />
print '8. List of logins'<br />
print '********************'<br />
print ''<br />
<br />
exec sp_helplogins<br />
go<br />
----------------------------------------------------------------------------------------------------------<br />
print ''<br />
print '9. List of users per role'<br />
print '*******************************'<br />
print ''<br />
<br />
exec sp_helpsrvrolemember<br />
go<br />
----------------------------------------------------------------------------------------------------------<br />
print ''<br />
print '10.List of special users per database'<br />
print '*************************************'<br />
print ''<br />
<br />
<br />
declare @name sysname,<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>@SQL nvarchar(600)<br />
<br />
if exists (select [id] from tempdb..sysobjects where [id] = OBJECT_ID ('tempdb..#tmpTable'))<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>drop table #tmpTable<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span><br />
CREATE TABLE #tmpTable (<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>[DATABASE_NAME] sysname NOT NULL ,<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>[USER_NAME] sysname NOT NULL,<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>[ROLE_NAME] sysname NOT NULL)<br />
<br />
declare c1 cursor for<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>select name from master.dbo.sysdatabases<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span><br />
open c1<br />
fetch c1 into @name<br />
while @@fetch_status >= 0<br />
begin<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>select @SQL =<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>'insert into #tmpTable<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span> select N'''+ @name + ''', a.name, c.name<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>from ' + QuoteName(@name) + '.dbo.sysusers a<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>join ' + QuoteName(@name) + '.dbo.sysmembers b on b.memberuid = a.uid<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>join ' + QuoteName(@name) + '.dbo.sysusers c on c.uid = b.groupuid<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>where a.name != ''dbo'''<br />
<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>/* <span class="Apple-tab-span" style="white-space: pre;"> </span>Insert row for each database */<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>execute (@SQL)<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>fetch c1 into @name<br />
end<br />
close c1<br />
deallocate c1<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span><br />
select * from #tmpTable<br />
<br />
drop table #tmpTable<br />
go<br />
----------------------------------------------------------------------------------------------------------<br />
print ''<br />
print '11. Information about remote servers '<br />
print '*****************************************'<br />
print ''<br />
<br />
Print 'Linked Servers'<br />
print ''<br />
<br />
exec sp_linkedservers<br />
<br />
print 'linked Server login mappings'<br />
print ''<br />
<br />
exec sp_helplinkedsrvlogin<br />
<br />
print 'Remote Logins'<br />
print ''<br />
<br />
--exec sp_helpremotelogin<br />
<br />
go<br />
----------------------------------------------------------------------------------------------------------<br />
print ''<br />
print '12. List of jobs '<br />
print '*******************'<br />
print ''<br />
<br />
exec msdb..sp_help_job<br />
go<br />
----------------------------------------------------------------------------------------------------------<br />
<br />
print ''<br />
print '13. Cache Hit Ratio '<br />
print '*******************'<br />
print ''<br />
<br />
select <span class="Apple-tab-span" style="white-space: pre;"> </span>distinct counter_name,<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>(select isnull(sum(convert(dec(15,0),B.cntr_value)),0)<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>from <span class="Apple-tab-span" style="white-space: pre;"> </span>master..sysperfinfo as B (nolock)<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>where <span class="Apple-tab-span" style="white-space: pre;"> </span>Lower(B.counter_name) like '%hit ratio%'<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>and<span class="Apple-tab-span" style="white-space: pre;"> </span>A.counter_name = B.counter_name) as CurrHit,<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>(select isnull(sum(convert(dec(15,0),B.cntr_value)),0)<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>from <span class="Apple-tab-span" style="white-space: pre;"> </span>master..sysperfinfo as B (nolock)<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>where <span class="Apple-tab-span" style="white-space: pre;"> </span>Lower(B.counter_name) like '%hit ratio base%'<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>and<span class="Apple-tab-span" style="white-space: pre;"> </span>lower(B.counter_name) = (lower(ltrim(rtrim(A.counter_name))) + ' base')) as CurrBase,<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>(select isnull(sum(convert(dec(15,0),B.cntr_value)),0)<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>from <span class="Apple-tab-span" style="white-space: pre;"> </span>master..sysperfinfo as B (nolock)<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>where <span class="Apple-tab-span" style="white-space: pre;"> </span>Lower(B.counter_name) like '%hit ratio%'<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>and<span class="Apple-tab-span" style="white-space: pre;"> </span>A.counter_name = B.counter_name) /<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>(select isnull(sum(convert(dec(15,0),B.cntr_value)),0)<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>from <span class="Apple-tab-span" style="white-space: pre;"> </span>master..sysperfinfo as B (nolock)<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>where <span class="Apple-tab-span" style="white-space: pre;"> </span>Lower(B.counter_name) like '%hit ratio base%'<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>and<span class="Apple-tab-span" style="white-space: pre;"> </span>lower(B.counter_name) = (lower(ltrim(rtrim(A.counter_name))) + ' base')) as HitRatio<br />
from <span class="Apple-tab-span" style="white-space: pre;"> </span>master..sysperfinfo as A (nolock)<br />
where <span class="Apple-tab-span" style="white-space: pre;"> </span>Lower(A.counter_name) like '%hit ratio%'<br />
and <span class="Apple-tab-span" style="white-space: pre;"> </span>Lower(A.counter_name) not like '%hit ratio base%'<br />
<br />
-- Audit list as a double verification<br />
<br />
select counter_name,isnull(sum(convert(dec(15,0),cntr_value)),0) as Value<br />
from <span class="Apple-tab-span" style="white-space: pre;"> </span>master..sysperfinfo (nolock)<br />
where <span class="Apple-tab-span" style="white-space: pre;"> </span>Lower(counter_name) like '%hit ratio%'<br />
or <span class="Apple-tab-span" style="white-space: pre;"> </span>Lower(counter_name) like '%hit ratio base%'<br />
group by counter_name<br />
<br />
go<br />
----------------------------------------------------------------------------------------------------------<br />
<br />
print ''<br />
print '14. SP_WHO '<br />
print '***********'<br />
print ''<br />
exec sp_who<br />
exec sp_who2<br />
go<br />
<br />
----------------------------------------------------------------------------------------------------------<br />
<br />
print ''<br />
print '14. SP_LOCKS '<br />
print '***********'<br />
print ''<br />
exec sp_lock<br />
<br />
go<br />
<br />
<br />
set nocount on<br />
use msdb<br />
go<br />
Print ''<br />
Print 'Linked Servers'<br />
print '***********'<br />
print ''<br />
<br />
exec sp_linkedservers<br />
<br />
Print ''<br />
print '15. Maintenance Plans'<br />
print '***********'<br />
print ''<br />
go<br />
<br />
select @@servername "ServerName", smp.plan_id, plan_name, owner, smpd.database_name, smpj.job_id, sj.name, sjs.name "Job Part Name", sjs.enabled "Job Enabled", sjs.freq_type "Frequency", sjs.active_start_time "Job Start Time"<br />
from sysdbmaintplans smp, sysdbmaintplan_databases smpd, sysdbmaintplan_jobs smpj, sysjobs sj, sysjobschedules sjs<br />
where smp.plan_id = smpd.plan_id and smp.plan_id = smpj.plan_id and sj.job_id = smpj.job_id and sj.job_id = sjs.job_id<br />
go<br />
Print ''<br />
print 'Maintenance Plan 2005'<br />
SELECT sv.name AS [Name], sv.job_id AS [JobID],sv.originating_server AS [OriginatingServer],<br />
CAST(sv.enabled AS bit) AS [IsEnabled],<br />
ISNULL(sv.description,N'') AS [Description],sv.start_step_id AS [StartStepID],ISNULL(suser_sname(sv.owner_sid), N'') AS [OwnerLoginName],<br />
sv.notify_level_eventlog AS [EventLogLevel],sv.notify_level_email AS [EmailLevel],sv.notify_level_netsend AS [NetSendLevel],<br />
sv.notify_level_page AS [PageLevel],sv.delete_level AS [DeleteLevel],sv.date_created AS [DateCreated],sv.date_modified AS [DateLastModified],<br />
sv.version_number AS [VersionNumber]FROM msdb.dbo.sysjobs_view AS sv<br />
go<br />
<br />
<br />
set dateformat dmy<br />
<br />
use msdb<br />
go<br />
<br />
select y.Server_name, y.database_name, y.database_creation_date, y.backup_start_Date, y.backup_finish_Date , y.backup_file, y.backup_size<br />
from (<br />
select c.Server_name, c.database_name, c.database_creation_date, c.backup_start_Date, c.backup_finish_Date , a.physical_device_name as backup_file, c.backup_size<br />
from Backupmediafamily a inner join backupset c on a.media_set_id=c.media_set_id<br />
inner join backupfile b on<br />
c.backup_set_id=b.backup_set_id and b.file_type='D'<br />
) y<br />
inner join<br />
(select distinct c.database_name, c.backup_finish_Date as backup_finish_date from Backupmediafamily a inner join backupset c on a.media_set_id=c.media_set_id<br />
inner join backupfile b on<br />
c.backup_set_id=b.backup_set_id and b.file_type='D' where c.backup_finish_date > getdate()-10<br />
) z<br />
on y.database_name=z.database_name and y.backup_finish_Date=z.backup_finish_date<br />
<br />
<br />
<br />
<br />
<br />
Print ''<br />
Print '16. OTHER'<br />
PRINT 'DTS Packages'<br />
print '***********'<br />
print ''<br />
<br />
go<br />
select @@servername "ServerName", sd.name, sd.id, sd.categoryid, sdc.name "Category", sd.description, owner, createdate<br />
from sysdtspackages sd, sysdtscategories sdc<br />
where sd.categoryid = sdc.id<br />
go<br />
exec sp_MSgetalertinfo<br />
<br />
print ''<br />
print 'Alerts settings'<br />
print '***********'<br />
print ''<br />
<br />
exec msdb..sp_help_alert<br />
<br />
Print ''<br />
Print 'Operators'<br />
print '***********'<br />
print ''<br />
<br />
EXECUTE sp_help_operator<br />
<br />
print ''<br />
print 'SQL Mails'<br />
EXECUTE master.dbo.xp_sqlagent_notify N'M',null,null,null,N'E'<br />
<br />
print ''<br />
Print 'SQL Agent Propertes'<br />
EXECUTE msdb.dbo.sp_get_sqlagent_properties<br />
<br />
print ''<br />
print 'Startup Parameters 01'<br />
exec master..xp_instance_regread N'HKEY_LOCAL_MACHINE',<br />
N'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer\Parameters', N'SQLarg0'<br />
<br />
print ''<br />
print 'Startup Parameters 02'<br />
exec master..xp_instance_regread N'HKEY_LOCAL_MACHINE',<br />
N'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer\Parameters', N'SQLarg1'<br />
<br />
print ''<br />
print 'Startup Parameters 03'<br />
exec master..xp_instance_regread N'HKEY_LOCAL_MACHINE',<br />
N'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer\Parameters', N'SQLarg2'<br />
<br />
print ''<br />
print 'Startup Parameters 04'<br />
exec master..xp_instance_regread N'HKEY_LOCAL_MACHINE',<br />
N'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer\Parameters', N'SQLarg3'<br />
<br />
-- Though there is very little chance of more than 4 parameters, still it is good to check.<br />
<br />
print ''<br />
print 'Startup Parameters 05'<br />
exec master..xp_instance_regread N'HKEY_LOCAL_MACHINE',<br />
N'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer\Parameters', N'SQLarg4'<br />
<br />
print ''<br />
print 'Startup Parameters 06'<br />
exec master..xp_instance_regread N'HKEY_LOCAL_MACHINE',<br />
N'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer\Parameters', N'SQLarg5'<br />
<br />
print ''<br />
<br />
--exec master..xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\Setup', N'SQLPath'<br />
<br />
<br />
print ''<br />
print 'Login Mode'<br />
exec master..xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer', 'LoginMode'<br />
go<br />
<br />
print ''<br />
Print 'Audit Level'<br />
<br />
exec master..xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer', N'AuditLevel'<br />
<br />
Print ''<br />
Print 'Clustered?'<br />
select convert(int, serverproperty(N'isclustered'))<br />
go<br />
Print ''<br />
Print 'Is Mapi set?'<br />
DECLARE @retval varchar(255) EXECUTE master.dbo.xp_instance_regread 'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft\Windows Messaging Subsystem','MAPIX',@retval OUTPUT SELECT @retval<br />
go<br />
<br />
Print ''<br />
Print 'Mail Account'<br />
exec master..xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer', N'MailAccountName'<br />
Print ''<br />
<br />
set nocount off<br />
<br />
<br />
print '******************************************************************'<br />
print ' FIM '<br />
print '******************************************************************'<br />
----------------------------------------------------------------------------------------------------------<br />
set nocount off<br />
<br />
-----------------------------<br />
print ''<br />
print 'deny logins'<br />
use master<br />
go<br />
select name,loginname,denylogin,createdate,hasaccess from syslogins where denylogin ='1'<br />
<br />
go<br />
<div>
<br /></div>
<br />
<br /></div>
Anonymoushttp://www.blogger.com/profile/01716610258406540066noreply@blogger.com0tag:blogger.com,1999:blog-8576109261886596415.post-45660998422322702752012-10-02T00:27:00.003+05:302012-10-02T00:27:42.128+05:30Verify Backup Set Commands<div dir="ltr" style="text-align: left;" trbidi="on">
Below is the list of command that we can use the verify the backup file.<br />
<br />
<br />
<br />
Information for media set of backup -> restore labelonly from disk = 'Path' (details about the media set on which backup is taken)<br />
<br />
Information to check the backup set is valid -> restore verify only from disk = 'path' (to check that my backup is valid)<br />
<br />
Information to check the details of the files in backup - > restore filelistonly from disk = 'path' (information about database files, size and files detail)<br />
<br />
Information to check the details of the backup file -> restore headeronly from disk = 'path'( details of backup,who has taken the backup,backup of which database, collation of database, compressed, backup of which server)<br />
</div>
Anonymoushttp://www.blogger.com/profile/01716610258406540066noreply@blogger.com0tag:blogger.com,1999:blog-8576109261886596415.post-79440419957146604312012-10-02T00:25:00.002+05:302012-10-02T00:25:36.783+05:30Script to find username and corresponding loginname for all user database in SQL Server 2005/2008/2008R2<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
create proc LoginsAndUsers<br />
as<br />
begin<br />
create table #systemdbs (name sysname)<br />
insert #systemdbs<br />
select 'master'<br />
union select 'msdb'<br />
union select 'model'<br />
union select 'tempdb'<br />
<br />
create table #dbusers (DatabaseName sysname, UserName sysname, GroupName sysname, LoginName sysname null,<br />
DefDBName sysname null, DefSchemaName sysname null, UserID int, SID varbinary(1000))<br />
<br />
create table #dbusersbuffer ( UserName sysname, GroupName sysname, LoginName sysname null,<br />
DefDBName sysname null, DefSchemaName sysname null, UserID int, SID varbinary(1000))<br />
<br />
<br />
<br />
declare @Command nvarchar(1012)<br />
set @Command= 'if not exists (select * from #systemdbs where name = ''?'') begin '+char(13)+<br />
+'use ? ; insert #dbusersbuffer exec sp_helpuser'+char(13)<br />
+'insert #dbusers select ''?'', * from #dbusersbuffer'+char(13)<br />
+'truncate table #dbusersbuffer'+char(13)<br />
+'end'<br />
print @Command<br />
<br />
exec sp_MSforeachdb @command1 = @Command<br />
<br />
select databasename,username,groupname,loginname,defdbname from #dbusers where username != 'dbo' and groupname !='public' order by DatabaseName<br />
<br />
drop table #dbusers<br />
drop table #dbusersbuffer<br />
drop table #systemdbs<br />
end<br />
go<br />
<br />
exec LoginsAndUsers<br />
drop proc loginsandusers<br />
<div>
<br /></div>
</div>
Anonymoushttp://www.blogger.com/profile/01716610258406540066noreply@blogger.com0tag:blogger.com,1999:blog-8576109261886596415.post-16936590390795719862012-10-02T00:24:00.003+05:302012-10-02T00:24:41.850+05:30Script to find username and corresponding loginname for all user database in SQL Server 2000<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
create proc LoginsAndUsers<br />
as<br />
begin<br />
create table #systemdbs (name sysname)<br />
insert #systemdbs<br />
select 'master'<br />
union select 'msdb'<br />
union select 'model'<br />
union select 'tempdb'<br />
<br />
create table #dbusers (DatabaseName sysname, UserName sysname, GroupName<br />
sysname, LoginName sysname null,DefDBName sysname null, UserID int, SID varbinary(1000))<br />
<br />
create table #dbusersbuffer ( UserName sysname, GroupName sysname, LoginName<br />
sysname null,DefDBName sysname null, UserID int, SID varbinary(1000))<br />
<br />
<br />
<br />
declare @Command nvarchar(1012)<br />
set @Command= 'if not exists (select * from #systemdbs where name = ''?'') begin '+char(13)+<br />
+'use [?] ; insert #dbusersbuffer exec sp_helpuser'+char(13)<br />
+'insert #dbusers select ''?'', * from #dbusersbuffer'+char(13)<br />
+'truncate table #dbusersbuffer'+char(13)<br />
+'end'<br />
print @Command<br />
<br />
exec sp_MSforeachdb @command1 = @Command<br />
<br />
select databasename,username,groupname,loginname,defdbname from #dbusers where username != 'dbo' and groupname !='public' order by DatabaseName<br />
<br />
drop table #dbusers<br />
drop table #dbusersbuffer<br />
drop table #systemdbs<br />
end<br />
go<br />
<br />
exec LoginsAndUsers<br />
drop proc loginsandusers<br />
<br />
</div>
Anonymoushttp://www.blogger.com/profile/01716610258406540066noreply@blogger.com0tag:blogger.com,1999:blog-8576109261886596415.post-38379507238448225172012-10-02T00:23:00.002+05:302012-10-02T00:23:25.673+05:30Script to find username and corresponding loginname in SQL SERVER 2005/2008/2008R2<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
The below script will assist you to find username and corresponding loginname in SQL SERVER 2005/2008/2008R2<br />
<br />
create proc LoginsAndUsers<br />
as<br />
begin<br />
create table #systemdbs (name sysname)<br />
insert #systemdbs<br />
select 'tempdb'<br />
<br />
create table #dbusers (DatabaseName sysname, UserName sysname, GroupName sysname, LoginName sysname null,<br />
DefDBName sysname null, DefSchemaName sysname null, UserID int, SID varbinary(1000))<br />
<br />
create table #dbusersbuffer ( UserName sysname, GroupName sysname, LoginName sysname null,<br />
DefDBName sysname null, DefSchemaName sysname null, UserID int, SID varbinary(1000))<br />
<br />
<br />
<br />
declare @Command nvarchar(1012)<br />
set @Command= 'if not exists (select * from #systemdbs where name = ''?'') begin '+char(13)+<br />
+'use ? ; insert #dbusersbuffer exec sp_helpuser'+char(13)<br />
+'insert #dbusers select ''?'', * from #dbusersbuffer'+char(13)<br />
+'truncate table #dbusersbuffer'+char(13)<br />
+'end'<br />
print @Command<br />
<br />
exec sp_MSforeachdb @command1 = @Command<br />
<br />
select databasename,username,groupname,loginname,defdbname from #dbusers where groupname !='public' order by DatabaseName<br />
<br />
drop table #dbusers<br />
drop table #dbusersbuffer<br />
drop table #systemdbs<br />
end<br />
go<br />
<br />
exec LoginsAndUsers<br />
drop proc loginsandusers<br />
<div>
<br /></div>
</div>
Anonymoushttp://www.blogger.com/profile/01716610258406540066noreply@blogger.com0tag:blogger.com,1999:blog-8576109261886596415.post-70679711070781787212012-10-02T00:21:00.002+05:302012-10-02T00:21:36.411+05:30Script to find username and corresponding loginname in SQL SERVER 2000<div dir="ltr" style="text-align: left;" trbidi="on">
The below script will list down all the username and its associated loginname in SQL Server 2000.<br />
<br />
<br />
create proc LoginsAndUsers<br />
as<br />
begin<br />
create table #systemdbs (name sysname)<br />
insert #systemdbs<br />
select 'tempdb'<br />
<br />
create table #dbusers (DatabaseName sysname, UserName sysname, GroupName<br />
sysname, LoginName sysname null,DefDBName sysname null, UserID int, SID varbinary(1000))<br />
<br />
create table #dbusersbuffer ( UserName sysname, GroupName sysname, LoginName<br />
sysname null,DefDBName sysname null, UserID int, SID varbinary(1000))<br />
<br />
<br />
<br />
declare @Command nvarchar(1012)<br />
set @Command= 'if not exists (select * from #systemdbs where name = ''?'') begin '+char(13)+<br />
+'use [?] ; insert #dbusersbuffer exec sp_helpuser'+char(13)<br />
+'insert #dbusers select ''?'', * from #dbusersbuffer'+char(13)<br />
+'truncate table #dbusersbuffer'+char(13)<br />
+'end'<br />
print @Command<br />
<br />
exec sp_MSforeachdb @command1 = @Command<br />
<br />
select databasename,username,groupname,loginname,defdbname from #dbusers where username != 'dbo' and groupname !='public' order by DatabaseName<br />
<br />
drop table #dbusers<br />
drop table #dbusersbuffer<br />
drop table #systemdbs<br />
end<br />
go<br />
<br />
exec LoginsAndUsers<br />
drop proc loginsandusers<br />
<br />
</div>
Anonymoushttp://www.blogger.com/profile/01716610258406540066noreply@blogger.com0tag:blogger.com,1999:blog-8576109261886596415.post-5234915738888047942012-10-02T00:18:00.001+05:302012-10-02T00:18:22.438+05:30Script to find the total used size of all database inside SQL Server<div dir="ltr" style="text-align: left;" trbidi="on">
The below script will give you the total used size of all database inside SQL Server.<br />
<br />
<br />
create table #dbusedsize ( used_mb int)<br />
DECLARE @SQL VARCHAR(1000)<br />
DECLARE @DB sysname<br />
DECLARE curDB CURSOR FORWARD_ONLY STATIC FOR<br />
SELECT [name]<br />
FROM master..sysdatabases<br />
WHERE DATABASEPROPERTYEX(name, 'Status') = 'ONLINE'<br />
OPEN curDB<br />
FETCH NEXT FROM curDB INTO @DB<br />
WHILE @@FETCH_STATUS = 0<br />
BEGIN<br />
SELECT @SQL = 'USE [' + @DB +']' + CHAR(13) + 'INSERT INTO #dbusedsize( used_mb )<br />
select FILEPROPERTY([name], ''SpaceUsed'')/128<br />
from sysfiles sf left outer join sysfilegroups sfg on sf.groupid=sfg.groupid<br />
order by 1' + CHAR(13)<br />
Exec (@SQL)<br />
FETCH NEXT FROM curDB INTO @DB<br />
END<br />
<br />
CLOSE curDB<br />
DEALLOCATE curDB<br />
select SUM(used_mb) from #dbusedsize<br />
drop table #dbusedsize<br />
</div>
Anonymoushttp://www.blogger.com/profile/01716610258406540066noreply@blogger.com0tag:blogger.com,1999:blog-8576109261886596415.post-24896903878919466522012-10-02T00:16:00.002+05:302012-10-02T00:16:49.140+05:30Script to find the total size of all the databases in SQL Server.<div dir="ltr" style="text-align: left;" trbidi="on">
Below is the script that will give you the total size of all the databases present in SQL Server.<br />
<br />
<br />
create table #dbsize ( size_mb int)<br />
DECLARE @SQL VARCHAR(1000)<br />
DECLARE @DB sysname<br />
DECLARE curDB CURSOR FORWARD_ONLY STATIC FOR<br />
SELECT [name]<br />
FROM master..sysdatabases<br />
WHERE DATABASEPROPERTYEX(name, 'Status') = 'ONLINE'<br />
OPEN curDB<br />
FETCH NEXT FROM curDB INTO @DB<br />
WHILE @@FETCH_STATUS = 0<br />
BEGIN<br />
SELECT @SQL = 'USE [' + @DB +']' + CHAR(13) + 'INSERT INTO #dbsize( size_mb )<br />
select size/128<br />
from sysfiles sf left outer join sysfilegroups sfg on sf.groupid=sfg.groupid<br />
order by 1' + CHAR(13)<br />
Exec (@SQL)<br />
FETCH NEXT FROM curDB INTO @DB<br />
END<br />
<br />
CLOSE curDB<br />
DEALLOCATE curDB<br />
select SUM(size_mb) from #dbsize<br />
drop table #dbsize<br />
</div>
Anonymoushttp://www.blogger.com/profile/01716610258406540066noreply@blogger.com0tag:blogger.com,1999:blog-8576109261886596415.post-91909379680940683282012-10-02T00:10:00.001+05:302012-10-02T00:47:17.108+05:30Issue 4 :- Applying SP2 on SQL Server 2008 and it shows black CMD screen and then it disappears.<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<span style="font-family: Times, Times New Roman, serif;">Hi,</span><br />
<span style="font-family: Times, Times New Roman, serif;"><br /></span>
<span style="font-family: Times, Times New Roman, serif;">Once I faced a issue when I was applying SP2 on SQL Server 2008R2. Whenever I clicked the setup icon the black CMD screen runs and disappears. Nothing happened after that and every time I click the setup icon the result is same. Then I thought that SP2 might me corrupt, so i downloaded a new SP2, but it also behaved like the earlier one. After much effort I was able to rectify the problem using the below steps.</span><br />
<span style="font-family: Times, Times New Roman, serif;"><br /></span>
<span style="font-family: Times, Times New Roman, serif;">The .Net error : the SQLsetlog gives below error</span><br />
<span style="font-family: Times, Times New Roman, serif;">Error: Cannot load .Net security policy file</span><br />
<span style="font-family: Times, Times New Roman, serif;">Error: InitializeSqlSetupCodeGroupCore(32bit) failed</span><br />
<span style="font-family: Times, Times New Roman, serif;">Error: InitializeSqlSetupCodeGroup failed: 0x80004005</span><br />
<span style="font-family: Times, Times New Roman, serif;">Setup closed with exit code: 0x80004005</span><br />
<span style="font-family: Times, Times New Roman, serif;">Ran in cmd : C:\Windows\Microsoft.NET\Framework\v2.0.50727\caspol.exe -machine -reset</span><br />
<span style="font-family: Times, Times New Roman, serif;">if you have 64 bit then use Framework64</span><br />
</div>
Anonymoushttp://www.blogger.com/profile/01716610258406540066noreply@blogger.com12tag:blogger.com,1999:blog-8576109261886596415.post-61119028412878611062012-09-27T19:20:00.006+05:302012-09-27T19:20:59.664+05:30How to find backup status of databases which are online<div dir="ltr" style="text-align: left;" trbidi="on">
We need to find the backup status of all database which are currently in online state . This script with not include database that is offline or in recovery state.<br />
<br />
DECLARE @Baksql VARCHAR(8000) DECLARE @BackupFolder VARCHAR(100) DECLARE @BackupFile VARCHAR(100) DECLARE @BAK_PATH VARCHAR(4000) DEclare @BackupDate varchar(100) <br />
-- Setting value of backup date and folder of the backup<br />
SET @BackupDate = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR,GETDATE(),120),'-',''),':',''),' ','_') -- 20110517_182551 SET @BackupFolder = 'D:\DBBackups\' SET @Baksql = '' <br />
-- Declaring cursor<br />
DECLARE c_bakup CURSOR FAST_FORWARD READ_ONLY FOR SELECT NAME FROM SYS.DATABASES WHERE state_desc = 'ONLINE'<br />
-- Consider databases which are online<br />
AND database_id > 4 <br />
-- Exluding system databases <br />
-- Opening and fetching next values from sursor<br />
OPEN c_bakup FETCH NEXT FROM c_bakup INTO @BackupFile WHILE @@FETCH_STATUS = 0 BEGIN SET @BAK_PATH = @BackupFolder + @BackupFile <br />
-- Creating dynamic script for every databases backup<br />
SET @Baksql = 'BACKUP DATABASE ['+@BackupFile+'] TO DISK = '''+@BAK_PATH+'_FullBackup_'+@BackupDate+'.bak'' WITH INIT;' <br />
-- Executing dynamic query<br />
PRINT (@Baksql) EXEC(@Baksql) <br />
-- Opening and fetching next values from sursor<br />
FETCH NEXT FROM c_bakup INTO @BackupFile END <br />
-- Closing and Deallocating cursor<br />
CLOSE c_bakup DEALLOCATE c_bakup</div>
Anonymoushttp://www.blogger.com/profile/01716610258406540066noreply@blogger.com0tag:blogger.com,1999:blog-8576109261886596415.post-89536214931539242842012-09-27T19:14:00.001+05:302012-09-27T19:14:08.262+05:30How to check status of SQL services using TSQL<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: Times, Times New Roman, serif;">To check the SQL Server services or any other service status using SSMS you can
execute the extended stored procedure xp_servicecontrol. </span><br />
<span style="font-family: Times, Times New Roman, serif;"><br /></span>
<span style="font-family: Times, Times New Roman, serif;"><b><span style="color: red;">EXEC
xp_servicecontrol N'Querystat|Start|Stop’,N'<wbr></wbr>Service Name' </span></b></span><span style="font-family: Times, Times New Roman, serif;"><br /></span>
<span style="font-family: Times, Times New Roman, serif;"><br /></span>
<span style="font-family: Times, Times New Roman, serif;">E.g :- </span><br />
<pre><span style="font-family: Times, Times New Roman, serif;">
</span></pre>
<pre><span style="font-family: Times, Times New Roman, serif;">EXEC xp_servicecontrol N'querystate',N'MSSQLServer'</span></pre>
<pre><span style="font-family: Times, Times New Roman, serif;"> </span></pre>
<span style="font-family: Times, Times New Roman, serif;">EXEC xp_servicecontrol N'querystate',N'<wbr></wbr>SQLServerAGENT' </span></div>
Anonymoushttp://www.blogger.com/profile/01716610258406540066noreply@blogger.com0tag:blogger.com,1999:blog-8576109261886596415.post-12925699624727432032012-09-11T19:02:00.000+05:302012-09-12T10:50:12.988+05:30How to find the owner of the maintenance plans in SQL SERVER 2000/2005/2008/2008R2<div dir="ltr" style="text-align: left;" trbidi="on">
Run the below script and it will tell you the owner of the maintenance plan.<br />
<br />
For SQL 2000.<br />
<br />
select plan_name,owner from dbo.sysdbmaintplans<br />
<br />
For SQL 2005/2008/2008R2<br />
<br />
select * from dbo.sysmaintplan_plans<br />
<br />
If you want to change the maintenance plan owner then please use the below command.<br />
<br />
For SQL 2000<br />
<br />
update dbo.sysdbmaintplans set owner ='sa' where owner ='LoginName'<br />
<br />
For SQL 2005<br />
<br />
<br />
UPDATE<br />
msdb.dbo.sysdtspackages90<br />
SET<br />
OWNERSID = SUSER_SID('YOUR_DOMAIN\APPROPRIATE_ACCOUNT')<br />
WHERE<br />
OWNERSID = SUSER_SID('YOUR_DOMAIN\OLD_ACCOUNT')<br />
<br />
For SQL 2008/2008R2<br />
<br />
<br />
update msdb.dbo.sysssispackages set [ownersid] = suser_sid('sa') where [name] = 'MaintenancePlan'<br />
<br /></div>
Anonymoushttp://www.blogger.com/profile/01716610258406540066noreply@blogger.com3tag:blogger.com,1999:blog-8576109261886596415.post-49751570404314356512012-09-11T18:57:00.001+05:302012-09-12T10:50:52.180+05:30How to change the collation of the server.<div dir="ltr" style="text-align: left;" trbidi="on">
Run the below command in CMD and it will change the collation of the server. Master database will be rebuild.<br />
<br />
setup.exe /Quiet /ACTION=REBUILDDATABASE /INSTANCENAME=Instance2 /SQLSYSADMINACCOUNTS=server\Administrator /SAPWD=sa123 /SQLCOLLATION=SQL_Latin1_General_CP1_CS_AS </div>
Anonymoushttp://www.blogger.com/profile/01716610258406540066noreply@blogger.com0tag:blogger.com,1999:blog-8576109261886596415.post-61911471941832466322012-09-11T18:55:00.004+05:302012-09-12T10:53:29.466+05:30Script to list all indexes in the databases.<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: Courier New, Courier, monospace;">Run the below script on the database and it will list down all the indexes present inside the database.</span><br />
<span style="font-family: Courier New, Courier, monospace;"><br />
<br />
</span><br />
<div class="MsoNormal">
<span style="font-family: Courier New, Courier, monospace;">SELECT SchemaName = schema_name(schema_id), <o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Courier New, Courier, monospace;"> TableName = object_name(t.object_id), <o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Courier New, Courier, monospace;"> ColumnName = c.name, <o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Courier New, Courier, monospace;"> [Unique] = CASE <o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Courier New, Courier, monospace;"> WHEN i.is_unique = 1 THEN
'Yes' <o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Courier New, Courier, monospace;"> ELSE 'No' <o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Courier New, Courier, monospace;"> END, <o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Courier New, Courier, monospace;"> [Clustered] = CASE <o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Courier New, Courier, monospace;"> WHEN i.index_id = 1
THEN 'C' <o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Courier New, Courier, monospace;"> ELSE 'NC' <o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Courier New, Courier, monospace;"> END, <o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Courier New, Courier, monospace;"> Ordinal = key_ordinal,<o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Courier New, Courier, monospace;"> IndexName = i.Name <o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Courier New, Courier, monospace;">FROM sys.indexes i <o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Courier New, Courier, monospace;"> INNER JOIN sys.tables t <o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Courier New, Courier, monospace;"> ON i.object_id = t.object_id <o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Courier New, Courier, monospace;"> INNER JOIN sys.index_columns ic <o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Courier New, Courier, monospace;"> ON ic.object_id = t.object_id <o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Courier New, Courier, monospace;"> AND ic.index_id = i.index_id <o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Courier New, Courier, monospace;"> INNER JOIN sys.columns c <o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Courier New, Courier, monospace;"> ON c.object_id = t.object_id <o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Courier New, Courier, monospace;"> AND ic.column_id = c.column_id <o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Courier New, Courier, monospace;">ORDER BY SchemaName, <o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Courier New, Courier, monospace;"> TableName, <o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Courier New, Courier, monospace;"> IndexName, <o:p></o:p></span></div>
<div class="MsoNormal">
<span style="font-family: Courier New, Courier, monospace;"> key_ordinal</span><span style="color: red; font-family: 'Courier New'; font-size: 10pt;"><o:p></o:p></span></div>
</div>
Anonymoushttp://www.blogger.com/profile/01716610258406540066noreply@blogger.com0tag:blogger.com,1999:blog-8576109261886596415.post-28977542751031514442012-09-11T18:52:00.001+05:302012-09-11T20:09:35.601+05:30Issue 3 :-TSQL command to check if the Login has access to server<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: Courier New, Courier, monospace; line-height: 115%;">Issue :-</span><br />
<span style="font-family: Courier New, Courier, monospace;"><span style="line-height: 115%;"><br /></span>
<span style="line-height: 115%;">When job fails and job owner is
a domain login which do not have access to server </span></span><br />
<span style="font-family: Courier New, Courier, monospace;"><span style="line-height: 115%;"><br /></span>
<span style="line-height: 115%;"><br /></span>
<span style="line-height: 14px;">Cause :-</span></span><br />
<span style="font-family: Courier New, Courier, monospace;"><span style="line-height: 14px;"><br /></span>
<span style="line-height: 14px;">SQL agent services will not be running with a domain account thus it is not able to verify the domain login. Also the account doesn't exist on domain any more.</span></span><br />
<span style="font-family: Courier New, Courier, monospace;"><span style="line-height: 14px;"><br /></span>
<span style="line-height: 14px;">Remediation Steps :-</span></span><br />
<span style="font-family: Courier New, Courier, monospace;"><span style="line-height: 14px;"><br /></span>
<span style="line-height: 14px;">1. </span>Xp_logininfo (‘account
name’). Run the command and if it return NULL that means the server is not able to verify the domain account.</span><br />
<span style="font-family: Courier New, Courier, monospace;"><br /></span>
<span style="font-family: Courier New, Courier, monospace;">2. Change the job owner to SA and re-run the job. It will run successfully.</span><br />
<div class="MsoNormal">
<span style="color: blue; font-family: "Courier New"; font-size: 10.0pt; mso-no-proof: yes;"><o:p></o:p></span></div>
</div>
Anonymoushttp://www.blogger.com/profile/01716610258406540066noreply@blogger.com0