Random scripts

R_time_use

========================================

SELECT @@VERSION

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

========================================

who took the backup:

select user_name, backup_start_date from backupset where database_name='mysupplier' and type='D' order by backup_Start_date desc

========================================

To minimise the disk space the test copy uses I tried to shrink the log using:

BACKUP LOG MyDatabase WITH TRUNCATE_ONLY

This returns an error: "The log was not truncated because records at the beginning of the log are pending replication. Ensure the Log Reader Agent is running or use sp_repldone to mark transactions as distributed."

********************

Do the following

Run sp_replicationdboption 'database name','publish',true'

then run sp_repldone with the following parameters

EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1

then unpublish the database

sp_replicationdboption 'database name','publish','false'

Run

dbcc traceon(3604)

dbcc opentran --- against the database to ensure there are no replicated transactions.

At this point you should be able to truncate the log

========================================

/*

This script used for list all database space allocated and its free space

revision history :

28 Feb 2007 - Solihin - www.solihinho.wordpress.com

compatibility : SQL Server 2000 and SQL 2005

*/

SET NOCOUNT ON

IF OBJECT_ID('tempdb..#DBSTATS') IS NOT NULL

BEGIN

DROP TABLE #DBSTATS

END

CREATE TABLE #DBSTATS (

dbname sysname,

lname sysname,

usage varchar(20),

[size] decimal(9, 2) NULL ,

[used] decimal(9, 2) NULL

)

IF OBJECT_ID('tempdb..#temp_log') IS NOT NULL

BEGIN

DROP TABLE #temp_log

END

CREATE TABLE #temp_log

(

DBName sysname,

LogSize real,

LogSpaceUsed real,

Status int

)

IF OBJECT_ID('tempdb..#temp_sfs') IS NOT NULL

BEGIN

DROP TABLE #temp_sfs

END

CREATE TABLE #temp_sfs

(

fileid int,

filegroup int,

totalextents int,

usedextents int,

name varchar(1024),

filename varchar(1024)

)

DECLARE @dbname sysname

,@sql varchar(8000)

IF OBJECT_ID('tempdb..#temp_db') IS NOT NULL

BEGIN

DROP TABLE #temp_db

END

SELECT name INTO #temp_db

FROM master.dbo.sysdatabases

WHERE DATABASEPROPERTY(name,'IsOffline') = 0

AND has_dbaccess(name) = 1

ORDER BY name

WHILE (1 = 1)

BEGIN

SET @dbname = NULL

SELECT TOP 1 @dbname = name

FROM #temp_db

ORDER BY name

IF @dbname IS NULL

GOTO _NEXT

SET @sql = ' USE ' + @dbname + '

TRUNCATE TABLE #temp_sfs

INSERT INTO #temp_sfs

EXECUTE(''DBCC SHOWFILESTATS'')

INSERT INTO #DBSTATS (DBNAME, LNAME, USAGE, [SIZE], [USED])

SELECT db_name(), NAME, ''Data''

, totalextents * 64.0 / 1024.0

, usedextents * 64.0 / 1024.0

FROM #temp_sfs

INSERT INTO #DBSTATS (DBNAME, LNAME, USAGE, [SIZE], [USED])

SELECT db_name(), name, ''Log'', null, null

FROM sysfiles

WHERE status & 0x40 = 0x40'

EXEC(@sql)

DELETE FROM #temp_db WHERE name = @dbname

END

_NEXT:

INSERT INTO #Temp_Log

EXECUTE ('DBCC SQLPERF(LOGSPACE)')

UPDATE #DBSTATS

SET SIZE = B.LogSize

, USED = LogSize * LogSpaceUsed / 100

FROM #DBSTATS A

INNER JOIN #Temp_Log B

ON (A.DBNAME = B.DBNAME)AND(A.Usage = 'LOG')

SELECT dbname AS [database name],

lname AS [logical data name],

usage,

[size] AS [space allocated (MB)],

used AS[space used (MB)],

[size] - used AS [free space (MB)],

cast(used/[size]*100 AS numeric(9,2)) AS [space used %],

cast(100-(used/[size]*100) AS numeric(9,2)) AS [free space %]

FROM #DBSTATS

ORDER BY dbname, usage

DROP TABLE #DBSTATS

DROP TABLE #temp_db

DROP TABLE #temp_sfs

DROP TABLE #temp_log

SET NOCOUNT OFF

========================================

USE dbname;

GO

DBCC SHRINKFILE (filename or fileid,MB)

========================================

Separating the log files from data files:

1 Backup Transaction Log.

2 Shrink the Transaction Log Files

3 Allow the initial transaction log file to auto grow (currently auto grow is disabled)

4 Delete the newly added transaction log file.

5 Note down the Database Owner

6 Detach the Database

7 Move the Transaction Log file from R Drive to T Drive

8 Attach the database (point transaction log to new location) under same database owner.

9 Health check on Database.

10 Same steps should be done for each database

========================================

Finding user tables:

select distinct name from sysobjects where xtype='U'

========================================

Retrive latest backup from QA :

SELECT db.name AS [Database],

max(backup_finish_date)

AS [LastBackupDate]

FROM

[master].[dbo].[sysdatabases]

db LEFT OUTER JOIN

[msdb].[dbo]

.[backupset] bs

ON bs.database_name

= db.name

AND bs.type = 'D'

GROUP BY db.name

ORDER BY db.name

========================================

REPAIR_ALLOW_DATA_LOSS:

------------------------

ALTER DATABASE BIJOU

SET single_user WITH ROLLBACK IMMEDIATE;

go

DBCC checkdb ('BIJOU', repair_allow_data_loss);

go

----------------

SET multi_user WITH ROLLBACK IMMEDIATE;

------

dbcc checkdb(BIJOU)

----

========================================

Rename DB:

USE master

ALTER DATABASE career_res

SET single_user

GO

ALTER DATABASE career_res

MODIFY NAME = CAREER

GO

ALTER DATABASE CAREER

SET multi_user

GO

========================================

Remove transaction log files from disk :

Option 1

----------------

Begin Tran

declare @Date nvarchar(50)

declare @Time datetime

set @Time = DateAdd(hh, -72, GetDate())

set @Date = (Select Replace(Convert(nvarchar, @Time, 111), '/', '-') + 'T' + Convert(nvarchar, @Time, 108))

Select @Time,@Date

EXECUTE master.dbo.xp_delete_file 0,N'E:\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Transaction Log backups',N'Trn',@Date,1

RollBack Tran

-------------------------

Option 2

------------------

declare @dt datetime

select @dt=getdate()-3 --No days to delete

EXECUTE master.dbo.xp_delete_file 0,N'E:\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Transaction Log backups',N'*trn*',@dt

-----------------

========================================

Reindexing on all tables in a DB :

exec sp_msforeachtable @command1="Print '?' DBCC DBREINDEX ('?')"

Checkdb command for all the db's:

EXEC sp_MSforeachdb @command1="print '?' DBCC CHECKDB ('?')"

========================================

UPDATE ALL STATISTICS FOR ALL TABLES in the Database :

If you ever see a Query that is never Completing or very Slow on a Database. i.e If the performance Differs on TEST vs PROD Database, Check to see if the STATISTICS FOR the TABLE have been updated.

use this Query to UPDATE ALL STATISTICS FOR ALL TABLES in the Database,

USE <DATABASE NAME>

SELECT 'UPDATE STATISTICS '+TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

wHERE TABLE_TYPE='BASE TABLE'

Copy all from the ResultSet and Paste it in a new Query wWindow and Execute.

========================================

SQL SERVER - TempDB is Full. Move TempDB from one drive to another drive:

Make sure that TempDB is set to autogrow and do not set a maximum size for TempDB. If the current drive is too full to allow autogrow events, then arrange a

bigger drive, or add files to TempDB

on another device (using ALTER DATABASE as described below and allow those files to autogrow.

Move TempDB from one drive to another drive. There are major two reasons why TempDB needs to move from one drive to other drive.

1) TempDB grows big and the existing drive does not have enough space.

2) Moving TempDB to another file group which is on different physical drive helps to improve database disk read, as they can be read simultaneously.

Follow direction below exactly to move database and log from one drive (c:) to another drive (d:) and (e:).

Open Query Analyzer and connect to your server. Run this script to get the names of the files used for TempDB.

USE TempDB

GO

EXEC sp_helpfile

GO

Results will be something like:

name fileid filename filegroup size

------- ------ -------------------------------------------------------------- ---------- -------

tempdev 1 C:Program FilesMicrosoft SQL ServerMSSQLdatatempdb.mdf PRIMARY 16000 KB

templog 2 C:Program FilesMicrosoft SQL ServerMSSQLdatatemplog.ldf NULL 1024 KB

along with other information related to the database. The names of the files are usually tempdev and demplog by default. These names will be used in next

statement. Run following code, to move mdf and ldf files.

USE master

GO

ALTER DATABASE TempDB MODIFY FILE (NAME = tempdev, FILENAME = 'd:datatempdb.mdf')

GO

ALTER DATABASE TempDB MODIFY FILE (NAME = templog, FILENAME = 'e:datatemplog.ldf')

GO

The definition of the TempDB is changed. However, no changes are made to TempDB till SQL Server restarts. Please stop and restart SQL Server and it will

create TempDB files in new locations.

========================================

========================================

DYNAMIC MANAGEMENT VIEWS:

• • • Avg.Disk Sec/Read

    • 1 )Monitoring SQL Server disk I/O with PerfMon counters::