Find the list of server on Network

Method 1 :

Set SQLCMD mode => Use below query

!!SQLCMD -L

Method 2 :

use below query:

EXEC master..XP_CMDShell 'OSQL -L'

Moving the system Database in SQL Server

Moving the system Database in SQL Server

Below are the simple tips for moving system databases to differnet location.


Master Database =>

right-click on SQL Server in Enterprise Manager (EM) and choose Properties.
click the Startup Parameters

Replace the path for -d and -e parameter with new one

-d New patha\msater.mdf.
-l New patha\msatlog.ldf

Stop SQL Server and move the master data and log files to its new location.
start SQL Server and you have successfully moved your Master



Tempdb Database =>
open query analyzer and run the following query:

use master go
Alter database tempdb modify file (name = tempdev, filename = ‘E:Sqldata empdb.mdf’)
go
Alter database tempdb modify file (name = templog, filename = ‘E:Sqldata emplog.ldf’)
Go

Stop sql server move tempdb files to new location and delete the older one.
Restart sql server


MSDB Database =>

Right-click the SQL-Server name and click properties.
choose your startup parameters.
enter the parameter -T3608.
Restart SQL Server.
detach the database and move them to their appropriate place.
and reattach it

Checkpoint Vs LazyWriter

Difference between Checkpoint and LazyWriter

C: Flush dirty pages to Disk
L: Flush dirty pages to disk.

C: Flush only Data pages to disk
L: Check for available memory and removed Buffer pool (execution plan/compile plan/ Data pages /Memory objects)

C: Default, Occurs approximately every 1 minute
L: Occurs depending upon memory pressure and resource availability

C: Can be managed with sp_confige -recovery interval option
L:It is lazy, Sql server manages by its own.

C: Does not check the memory pressure
L:Monitor the memory pressure and try maintain the available free memory.

C: crash recovery process will be fast to read log as data file is updated.
L: No role in recovery

C: Occurs for any DDL statement
L: Occurs per requirement

C: Occurs before Backup/Detach command
L: Occurs per requirement

C: Depends upon the configuration setting, we can control.
L: Works on Least recent used pages and removed unused plans first, no user control.

C: for simple recovery it flush the tlog file after 70% full.
L: No effect on recovery model.

C: can manually /Forcefully run command “Checkpoint”
L: No command for Lazy Writer

C: Very Less performance impact
L: No performance impact

Checkpoint can be run manullay using command "Checkpoint":

Find log space and disk utilization

DECLARE @dbname VARCHAR(200),@sql VARCHAR(8000)
SET @sql = '' SET @dbname = ''

IF OBJECT_ID('tempdb..#T_Drive') IS NOT NULL DROP TABLE #T_Drive
IF OBJECT_ID('tempdb..#T_LogSpace') IS NOT NULL DROP TABLE #T_LogSpace
IF OBJECT_ID('tempdb..#T_DBInfo') IS NOT NULL DROP TABLE #T_DBInfo
IF OBJECT_ID('tempdb..#T_FileSpace') IS NOT NULL DROP TABLE #T_FileSpace
IF OBJECT_ID('tempdb..#T_Database') IS NOT NULL DROP TABLE #T_Database

CREATE TABLE #T_Drive([DriveName] VARCHAR(5) PRIMARY KEY,[FreeDriveSpace] BIGINT)

INSERT INTO #T_Drive EXEC master..xp_fixeddrives

CREATE TABLE #T_LogSpace ([DBName] VARCHAR(200) NOT NULL PRIMARY KEY,
[LogSize] MONEY NOT NULL,[LogPercentUsed] MONEY NOT NULL,[LogStatus] INT NOT NULL)

SELECT @sql = 'DBCC SQLPERF (LOGSPACE) WITH NO_INFOMSGS'

INSERT INTO #T_LogSpace EXEC(@sql)

CREATE TABLE #T_DBInfo (
[DBName] VARCHAR(200),[FileLogicalName] VARCHAR(200),[FileID] INT NOT NULL,[Filename] VARCHAR(250) NOT NULL,
[Filegroup] VARCHAR(100) NOT NULL,[FileCurrentSize] BIGINT NOT NULL,[FileMaxSize] VARCHAR(50) NOT NULL,
[FileGrowth] VARCHAR(50) NOT NULL,[FileUsage] VARCHAR(50) NOT NULL,[FileGrowthSize] BIGINT NOT NULL)

CREATE TABLE #T_Database ( [DBName] VARCHAR(200) PRIMARY KEY)

INSERT INTO #T_Database
SELECT DBName = LTRIM(RTRIM(name)) FROM master.dbo.sysdatabases
WHERE category IN ('0', '1','16')AND DATABASEPROPERTYEX(name, 'status') = 'ONLINE' ORDER BY name

CREATE TABLE #T_FileSpace ( [DBName] VARCHAR(200) NULL,[Fileid] INT NOT NULL,
[FileGroup] INT NOT NULL,[TotalExtents] MONEY NOT NULL,[UsedExtents] MONEY NOT NULL,
[FileLogicalName] sysname NOT NULL,[Filename] VARCHAR(1000) NOT NULL)

SELECT @dbname = MIN(dbname) FROM #T_Database

WHILE @dbname IS NOT NULL
BEGIN
SET @sql = 'USE ' + @dbname + '
INSERT INTO #T_DBInfo (
[DBName],[FileLogicalName],[FileID],[Filename],[Filegroup],[FileCurrentSize],
[FileMaxSize],[FileGrowth],[FileUsage],[FileGrowthSize])
SELECT DBName = ''' + @dbname + ''',FileLogicalName = SF.name,
FileID = SF.fileid, Filename = SF.filename, Filegroup = ISNULL(filegroup_name(SF.groupid),''''),
FileCurrentSize = (SF.size * 8)/1024, FileMaxSize =CASE SF.maxsize WHEN -1 THEN N''Unlimited''
ELSE CONVERT(VARCHAR(15), (CAST(SF.maxsize AS BIGINT) * 8)/1024) + N'' MB'' END,
FileGrowth = (case SF.status & 0x100000 when 0x100000 then
convert(varchar(3), SF.growth) + N'' %''
else
convert(varchar(15), ((CAST(SF.growth AS BIGINT) * 8)/1024)) + N'' MB'' end),
FileUsage = (case WHEN SF.status & 0x40 = 0x40 then ''Log'' else ''Data'' end),
FileGrowthSize = CASE SF.status & 0x100000 WHEN 0x100000 THEN
((((CAST(SF.size AS BIGINT) * 8)/1024)* SF.growth)/100) + ((CAST(SF.size AS BIGINT) * 8)/1024)
ELSE
((CAST(SF.size AS BIGINT) * 8)/1024) + ((CAST(SF.growth AS BIGINT) * 8)/1024)
END
FROM sysfiles SF
WHERE filename like ''%.ldf''
ORDER BY SF.fileid'

EXEC(@sql)

SET @sql = 'USE ' + @dbname + ' DBCC SHOWFILESTATS WITH NO_INFOMSGS'

INSERT INTO #T_FileSpace ([Fileid],[FileGroup],[TotalExtents],[UsedExtents],[FileLogicalName],[Filename])
EXEC (@sql)

UPDATE #T_FileSpace SET [DBName] = @dbname WHERE ISNULL([DBName],'') = ''

SELECT @dbname = MIN(dbname) FROM #T_Database WHERE dbname > @dbname
END

SELECT 'DBName' = DFI.DBName,
'FileLogicalName' = DFI.FileLogicalName,
'Filename' = DFI.[Filename],
'FileMBSize' = DFI.FileCurrentSize,
'FileGrowth' = DFI.FileGrowth,
'FileMBGrowth' = DFI.FileGrowthSize,
'DriveName' = TD.DriveName,
'DriveMBEmpty' = TD.FreeDriveSpace,
'FileMBUsed' = CAST(ISNULL(((FS.UsedExtents * 64.00) / 1024), LSP.LogSize *(LSP.LogPercentUsed/100)) AS BIGINT),
'FileMBEmpty' = DFI.FileCurrentSize - CAST(ISNULL(((FS.UsedExtents * 64.00) / 1024), LSP.LogSize *(LSP.LogPercentUsed/100)) AS BIGINT),
'LogSpaceUsed' = LSP.LogPercentUsed
FROM #T_DBInfo DFI
LEFT OUTER JOIN #T_Drive TD
ON LEFT(LTRIM(RTRIM(DFI.[FileName])),1) = LTRIM(RTRIM(TD.DriveName))
LEFT OUTER JOIN #T_FileSpace FS
ON LTRIM(RTRIM(FS.[Filename])) = LTRIM(RTRIM(DFI.[Filename]))
LEFT OUTER JOIN #T_LogSpace LSP
ON LtRIM(RTRIM(LSP.DBName)) = LTRIM(RTRIM(DFI.DBName))
ORDER BY 1

Rebuild MSDB Database

1.Put MSSQL into single user mode

1.Click -> START -> Microsoft SQL Server 2005 -> Configuration Tools – > SQL Server Configuration Manager
2.Right click on SQL Server and choose Properties
3.Click on the Advanced tab. Under Startup Parameters you will be adding the
following parameters to the beginning of the string: -m;-c;-T3608;



2.Restart SQL Server


3.Connect to SQL server through the Management Console or through command prompt using sqlcmd.
From this point on we will be using TSQL to issue the commands so click the New Query button on the top left. At this point you should be in the master database inside the query window.

Stop reporting or integration services it might stop you to connect in single user mode.


4.Detach the MSDB database using the following commands:

use master
go
sp_detach_db ‘msdb’
go


5.We need to move (or rename, I prefer moving them) the existing MDF and LDF files for the MSDB database so that we can recreate it.


1.Usually these files are located in the following directory:

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data

Your’s might differ.

2.Move (or rename) the MSDBDATA.mdf and MSDBLOG.ldf files.



6.Back to the Management Studio. Open up the instmsdb.sql file in a new query window. This file is usually located in the following directory:

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Install


7.Execute the file. If you see any errors about xp_cmdshell, just ignore them. They are common and the script will take care of it.


8.At this point you should have your MSDB database restored. The only thing left is cleanup.



9.Execute the following command to make sure that xp_cmdshell is once again set to disable for security reasons:

EXEC sp_configure ‘show advanced options’, 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure ‘xp_cmdshell’, 0
GO
RECONFIGURE WITH OVERRIDE
GO


10.Shutdown SQL Server


11.Go back into your Startup Paremeters for your server in the SQL Server Configuration Manager and removed the -c;-m;-T3608 parameters we added earlier.


12.Restart SQL Server

Everything should be cool at this point and you’ll be able to recreate any Maintenance Plans and Jobs.

Rebuilding or Restoring The SQL Server 2008 Master Database

Rebuild master database in 2008 is different from 2005 and 2000

We don't need externale binary files or setup cd, We can find setup.exe at below location:

"C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Release"


Follow the steps below to rebuild the Master Database.


1. From a command prompt window change to the following directory:

"C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Release"

2. Next paste the following into the command prompt window and press “Enter”:

Setup.Exe /Action=RebuildDatabase /InstanceName=MSSQLSERVER SAPWD=sa

If master.mdf file is missing manually copy the necessary file(s) from C:\Program Files\Microsoft SQL Server\MSSQL10.ONE2008\MSSQL\Binn\Templates

In case it doesn't work restore the master database on another server and replace the master data and log files on original server and then start repairing from setup file.


Restore latest backup of master and other databases for minimum data loss.

Tablediff utility using SQLCMD

The tablediff utility is used to compare the data in two tables and generate a Transact-SQL script to fix discrepancies at the destination server to bring the source and destination tables into convergence.


/*
Tablediff command from source to destination server with windows login, will give result to DiffsTable and will create query to fix the issue in MyTable1_MyTable2_diff.sql file
*/

EG : Run in SQLCMD mode:

!!"C:\Program Files\Microsoft SQL Server\90\COM\tablediff.exe" -sourceserver ADITYA-PC\ONE2005 -sourcedatabase payroll_AAA1 -sourcetable empmst -destinationserver ADITYA-PC\ONE2008 -destinationdatabase payroll_AAA -destinationtable empmst -et DiffsTable -f F:\MyTable1_MyTable2_diff.sql