Ads
04 April 2014
15 March 2014
11 March 2014
Data Compression
Syntax:-
=================
USE [student]
GO
Select
'ALTER TABLE ['+B.Name+'].['+ A.Name+ '] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)'
from sys.objects A INNER JOIN
sys.schemas B on A.Schema_id=B.Schema_id
where type = 'U' and A.name not like 'dtproperties'
ALTER TABLE [dbo].[Category] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)
For All Tables :-
===============
EXEC sp_MSforeachtable @command1 = 'alter table ? REBUILD WITH (DATA_COMPRESSION = PAGE);'
To Verify:-
========
SELECT
SCHEMA_NAME(sys.objects.schema_id) AS [SchemaName]
,OBJECT_NAME(sys.objects.object_id) AS [ObjectName]
,[rows]
,[data_compression_desc]
,[index_id] as [IndexID_on_Table]
FROM sys.partitions
INNER JOIN sys.objects
ON sys.partitions.object_id = sys.objects.object_id
WHERE data_compression > 0
AND SCHEMA_NAME(sys.objects.schema_id) <> 'SYS'
ORDER BY SchemaName, ObjectName
=================
USE [student]
GO
Select
'ALTER TABLE ['+B.Name+'].['+ A.Name+ '] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)'
from sys.objects A INNER JOIN
sys.schemas B on A.Schema_id=B.Schema_id
where type = 'U' and A.name not like 'dtproperties'
ALTER TABLE [dbo].[Category] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)
For All Tables :-
===============
EXEC sp_MSforeachtable @command1 = 'alter table ? REBUILD WITH (DATA_COMPRESSION = PAGE);'
To Verify:-
========
SELECT
SCHEMA_NAME(sys.objects.schema_id) AS [SchemaName]
,OBJECT_NAME(sys.objects.object_id) AS [ObjectName]
,[rows]
,[data_compression_desc]
,[index_id] as [IndexID_on_Table]
FROM sys.partitions
INNER JOIN sys.objects
ON sys.partitions.object_id = sys.objects.object_id
WHERE data_compression > 0
AND SCHEMA_NAME(sys.objects.schema_id) <> 'SYS'
ORDER BY SchemaName, ObjectName
28 February 2014
Retore T-Log With Move
Here in the syntax we can you with move.
RESTORE LOG [Student] FROM
DISK = N'P:\student_after_secfile.trn' WITH FILE = 1,NORECOVERY,
MOVE N'Student_new' TO N'P:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Student_new2.ndf',
NOUNLOAD, STATS = 10
GO
Another Scenario While Restoring Tlog and Space Crunch:-
RESTORE LOG [DB] FROM
DISK = N’D:\DB.trn’
WITH FILE = 1,
STANDBY = N’D:\ROLLBACK_UNDO_DB.BAK’,
MOVE N’DBFILENAME′ TO N’P:\Data\DB.ndf’,
NOUNLOAD, STATS = 10
GO
RESTORE LOG [Student] FROM
DISK = N'P:\student_after_secfile.trn' WITH FILE = 1,NORECOVERY,
MOVE N'Student_new' TO N'P:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Student_new2.ndf',
NOUNLOAD, STATS = 10
GO
Another Scenario While Restoring Tlog and Space Crunch:-
RESTORE LOG [DB] FROM
DISK = N’D:\DB.trn’
WITH FILE = 1,
STANDBY = N’D:\ROLLBACK_UNDO_DB.BAK’,
MOVE N’DBFILENAME′ TO N’P:\Data\DB.ndf’,
NOUNLOAD, STATS = 10
GO
24 February 2014
Collecting and Saving Configuration Information for a Number of Servers.
Help Link https://www.simple-talk.com/sql/sql-training/how-to-document-and-configure-sql-server-instance-settings/
PowerShell Script:-
=============
SET<#
This PowerShell routine creates an excel spreadsheet with the current configuration settings of all your servers. Each instance is on a different worksheet and the worksheets have the name of the instance. It works by using ODBC connections created locally on your workstation.
Once the spreadsheet is created, the PowerShell script is no longer needed since the data can be refreshed from within Excel. This means that you will have a live record of the configuration settings of your instances.
#>
#change these following settings to your environment
$DirectoryToSaveTo='E:\Scripts\'
$Filename='DatabaseConfiguration'
#
<#
Just make your list of servers here as an XML fragment
these instance names are actually User ODBC DSNs DSNs. Better for Excel.
We associate a version number with each just so you are sure
of a script that will run (You can't get this info from the DSN, and
Excel only allows one select statement in an Excel query
#>
$Servers=[xml] @'
'@
# constants.
$xlCenter=-4108
$xlTop=-4160
$xlOpenXMLWorkbook=[int]51
# and we put the queries in here
$SQL2005=@"
SELECT name, value, minimum, maximum, value_in_use as [Value in use],
description, is_dynamic AS [Dynamic?], is_advanced AS [Advanced?]
FROM sys.configurations ORDER BY name ;
"@
$SQL2008=@"
SELECT name, value, minimum, maximum, value_in_use as [Value in use],
description, is_dynamic AS [Dynamic?], is_advanced AS [Advanced?]
FROM sys.configurations ORDER BY name ;
"@
$SQL2000=@"
SELECT Name, c.Value, low AS [minimum], high AS [Maximum],
master.dbo.syscurconfigs.value AS [Value In Use],
c.comment AS [Description]
FROM master.dbo.spt_values v
INNER JOIN master.dbo.sysconfigures c ON number = c.config
INNER JOIN master.dbo.syscurconfigs ON number = master.dbo.syscurconfigs.config
WHERE type = 'C'
ORDER BY LOWER(name)
"@
# before we do anything else, are we likely to be able to save the file?
# if the directory doesn't exist, then create it
if (!(Test-Path -path "$DirectoryToSaveTo")) #create it if not existing
{
New-Item "$DirectoryToSaveTo" -type directory | out-null
}
$excel = New-Object -Com Excel.Application #open a new instance of Excel
$excel.Visible = $True #make it visible (for debugging more than anything)
$wb = $Excel.Workbooks.Add() #create a workbook
$currentWorksheet=1 #there are three open worksheets you can fill up
foreach ($server in $servers.serverlist.server)
{ #only create the worksheet if necessary
if ($currentWorksheet-lt 4) {$ws = $wb.Worksheets.Item($currentWorksheet)}
else {$ws = $wb.Worksheets.Add()} #add if it doesn't exist
$currentWorksheet += 1 #keep a tally
if ($server.version -eq 2005) {$SQL=$SQL2005} #get the right SQL Script
if ($server.version -eq 2008) {$SQL=$SQL2008}
if ($server.version -eq 2000) {$SQL=$SQL2000}
$currentName=$server.name # and name the worksheet
$ws.name=$currentName # so it appears in the tab
# note we create the query so that the user can run it to refresh it
$qt = $ws.QueryTables.Add("ODBC;DSN=$currentName", $ws.Range("A1"), $SQL)
# and execute it
if ($qt.Refresh()) #if the routine works OK
{
$ws.Activate()
$ws.Select()
$excel.Rows.Item(1).HorizontalAlignment = $xlCenter
$excel.Rows.Item(1).VerticalAlignment = $xlTop
$excel.Rows.Item(1).Orientation = -90
$excel.Columns.Item("G:H").NumberFormat = "[Red][=0]û;[Blue][=1]ü"
$excel.Columns.Item("G:H").Font.Name = "Wingdings"
$excel.Columns.Item("G:H").Font.Size = 12
$excel.Rows.Item("1:1").Font.Name = "Calibri"
$excel.Rows.Item("1:1").Font.Size = 11
$excel.Rows.Item("1:1").Font.Bold = $true
$Excel.Columns.Item(1).Font.Bold = $true
}
}
$filename=$filename -replace '[\\\/\:\.]',' ' #remove characters that can cause problems
$filename = "$DirectoryToSaveTo$filename.xlsx" #save it according to its title
if (test-path $filename ) { rm $filename } #delete the file if it already exists
$wb.SaveAs($filename, $xlOpenXMLWorkbook) #save as an XML Workbook (xslx)
$wb.Saved = $True #flag it as being saved
$wb.Close() #close the document
$Excel.Quit() #and the instance of Excel
$wb = $Null #set all variables that point to Excel objects to null
$ws = $Null #makes sure Excel deflates
$Excel=$Null #let the air out
# Hristo Deshev's Excel trick 'Pro Windows PowerShell' p380
[GC]::Collect()
==================================================================
SQL Required Info Querys :-
===================
SQL2005
----------
SELECT name, value, minimum, maximum, value_in_use as [Value in use],
description, is_dynamic AS [Dynamic?], is_advanced AS [Advanced?]
FROM sys.configurations ORDER BY name ;
SQL2008
----------
SELECT name, value, minimum, maximum, value_in_use as [Value in use],
description, is_dynamic AS [Dynamic?], is_advanced AS [Advanced?]
FROM sys.configurations ORDER BY name ;
SQL2000
----------
SELECT Name, c.Value, low AS [minimum], high AS [Maximum],
master.dbo.syscurconfigs.value AS [Value In Use],
c.comment AS [Description]
FROM master.dbo.spt_values v
INNER JOIN master.dbo.sysconfigures c ON number = c.config
INNER JOIN master.dbo.syscurconfigs ON number = master.dbo.syscurconfigs.config
WHERE type = 'C'
ORDER BY LOWER(name)
==================================================================
Subscribe to:
Posts (Atom)
-
ERROR: report server remote procedure call (RPC) end point. Verify that the Report Server Windows service is running, and then retry the o...
-
Error messages: · The process could not execute 'sp_repldone/sp_replcounters' on ' SCKNLSSI '. (Source:...
-
Error Message: Replication-Replication Distribution Subsystem: agent DBA\DBA-EPDW-EPDW-DB6C\DB6C-5 failed. The subscription(S) have been...