--say the name of the database is Manufacturing
BACKUP LOG [Manufacturing] TO DISK = N'Z:\DATABASE\TRANSACTION LOG BACKUPS\Manufacturing.trn' WITH NOFORMAT, NOINIT, NAME = N'Manufacturing-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
------------------------------------------------------------------
--NOINT --- append to the backup file
--INIT -- overwrite to the backup file
-------------------------------------------------------------------
NOFORMAT --- preserver the existing header format
FORMAT --- writes a new header format
---------------------------------------------------------------------
NOSKIP --check the expiration date of all backup sets
SKIP -- disable the checking
---------------------------------------------------------------------
NOREWIND --- without the tape rewind
REWIND -- with tape rewind
----------------------------------------------------------------------
DBCC SHRINKFILE (N'Manufacturing_Log' , 786)
Wednesday, September 30, 2009
Shrink Log File
--To shrink the log file
USE DatabaseName
Go
DBCC SHRINKFILE(, 1)
BACKUP LOGWITH TRUNCATE_ONLY
DBCC SHRINKFILE(, 1)GO
Go
--truncates the log by discarding all but the active log
--suggested to take a backup of the database immediatelty after this
USE DatabaseName
Go
DBCC SHRINKFILE(
BACKUP LOG
DBCC SHRINKFILE(
Go
--truncates the log by discarding all but the active log
--suggested to take a backup of the database immediatelty after this
String Functions
--replace function --- replacing p in the invoiceno to blank
replace(invoiceno, 'p','')
replace(invoiceno, 'p','')
Monday, September 21, 2009
Indexing
--Create index on table transdetails for the key field lotno
Create unique index idx_lotno on db.transdetails(lotno)
Create unique index idx_lotno on db.transdetails(lotno)
Friday, September 18, 2009
msdb backup
--You cannot restore system database backups to a different build of SQL Server
You cannot restore a backup of a system database (master, model, or msdb) on a server build that is different from the build on which the backup was originally performed. An attempt to perform such a restore causes the following error message to occur:
It is possible that the database schema for the system databases changed across server builds. In order to ensure that this change in schema does not cause any inconsistencies, one of the first checks that the RESTORE command performs is a comparison of the server build number on the backup file and the build number of the server on which the user is trying to perform the restore.
If the two builds are different, the error message occurs and the restore operation terminates abnormally. Note Installing a service pack or a hotfix build changes the server build number, and server builds are always incremental.Here are a few scenarios in which this problem may occur:
A user attempts to restore a system database on Server A from a backup taken on Server B. Servers A and B are on different server builds. For example, Server A might be on a RTM build and Server B might be on a service pack 1 (SP1) build.
A user attempts to restore a system database from a backup taken on the same server. However, the server was running a different build when the backup occurred. That is, the server was upgraded since the backup was performed.
You cannot restore a backup of a system database (master, model, or msdb) on a server build that is different from the build on which the backup was originally performed. An attempt to perform such a restore causes the following error message to occur:
It is possible that the database schema for the system databases changed across server builds. In order to ensure that this change in schema does not cause any inconsistencies, one of the first checks that the RESTORE command performs is a comparison of the server build number on the backup file and the build number of the server on which the user is trying to perform the restore.
If the two builds are different, the error message occurs and the restore operation terminates abnormally. Note Installing a service pack or a hotfix build changes the server build number, and server builds are always incremental.Here are a few scenarios in which this problem may occur:
A user attempts to restore a system database on Server A from a backup taken on Server B. Servers A and B are on different server builds. For example, Server A might be on a RTM build and Server B might be on a service pack 1 (SP1) build.
A user attempts to restore a system database from a backup taken on the same server. However, the server was running a different build when the backup occurred. That is, the server was upgraded since the backup was performed.
Thursday, September 17, 2009
Case statements
--using case statements to convert the integer to varchar of a month
select case when month(getdate()) = '1' then 'jan'
when month(getdate()) = '2' then 'feb'
when month(getdate()) = '3' then 'mar'
when month(getdate()) = '4' then 'apr'
when month(getdate()) = '5' then 'may'
when month(getdate()) = '6' then 'jun'
when month(getdate()) = '7' then 'jul'
when month(getdate()) = '8' then 'aug'
when month(getdate()) = '9' then 'sep'
when month(getdate()) = '10' then 'oct'
when month(getdate()) = '11' then 'nov'
when month(getdate()) = '12' then 'dec'
end
select case when month(getdate()) = '1' then 'jan'
when month(getdate()) = '2' then 'feb'
when month(getdate()) = '3' then 'mar'
when month(getdate()) = '4' then 'apr'
when month(getdate()) = '5' then 'may'
when month(getdate()) = '6' then 'jun'
when month(getdate()) = '7' then 'jul'
when month(getdate()) = '8' then 'aug'
when month(getdate()) = '9' then 'sep'
when month(getdate()) = '10' then 'oct'
when month(getdate()) = '11' then 'nov'
when month(getdate()) = '12' then 'dec'
end
Date Functions- get week, month, day of week
--get week of the given date as integer
select datepart(week,getdate())
--get day of week of given date as integer
select datepart(dw,getdate())
select datepart(week,getdate())
--get day of week of given date as integer
select datepart(dw,getdate())
--get month of given date as integer
select datepart(month,getdate())
--converting month to varchar from calendar
select case when month(getdate()) = '1' then 'jan'
when month(getdate()) = '2' then 'feb'
when month(getdate()) = '3' then 'mar'
when month(getdate()) = '4' then 'apr'
when month(getdate()) = '5' then 'may'
when month(getdate()) = '6' then 'jun'
when month(getdate()) = '7' then 'jul'
when month(getdate()) = '8' then 'aug'
when month(getdate()) = '9' then 'sep'
when month(getdate()) = '10' then 'oct'
when month(getdate()) = '11' then 'nov'
when month(getdate()) = '12' then 'dec'
end
Get FinYear
-- user defined funtion to get financial year starting from 01st Jul to 30th June
ALTER FUNCTION [dbo].[ufGetFinYr]
(
@CurDate datetime
)
RETURNS varchar(9) AS
BEGIN
Declare @CurrentMonth tinyint,@FinYear varchar(9)
select @CurrentMonth = month(@CurDate)
if @CurrentMonth >=1 and @CurrentMonth <=6 select @FinYear = convert(varchar,Year(@CurDate)-1) + '-' + convert(varchar,Year(@CurDate)) else select @FinYear = convert(varchar,Year(@CurDate)) + '-' + convert(varchar,Year(@CurDate)+1) return @FinYear END
--to query this
select dbo.ufgetfinyr(getdate())
ALTER FUNCTION [dbo].[ufGetFinYr]
(
@CurDate datetime
)
RETURNS varchar(9) AS
BEGIN
Declare @CurrentMonth tinyint,@FinYear varchar(9)
select @CurrentMonth = month(@CurDate)
if @CurrentMonth >=1 and @CurrentMonth <=6 select @FinYear = convert(varchar,Year(@CurDate)-1) + '-' + convert(varchar,Year(@CurDate)) else select @FinYear = convert(varchar,Year(@CurDate)) + '-' + convert(varchar,Year(@CurDate)+1) return @FinYear END
--to query this
select dbo.ufgetfinyr(getdate())
Wednesday, September 16, 2009
Duplicate records in a table
--to find out duplicate records in a table
select salesordernoitem from tbltransdetails group by salesordernoitem
having count(salesordernoitem) > 1
select salesordernoitem from tbltransdetails group by salesordernoitem
having count(salesordernoitem) > 1
Search a string in all stored procedures
-- To search string "repid" through all stored procedures this limits to 4000 characters
SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%repid%'
AND ROUTINE_TYPE='PROCEDURE'
-- To search string "repid" through all stored procedures
SELECT
sys.objects.name,
sys.objects.type_desc,
sys.sql_modules.definition
FROM
sys.sql_modules inner join sys.objects
on sys.sql_modules.object_id = sys.objects.object_id
WHERE
sys.sql_modules.definition LIKE '%tblShipToMaster%'
Find all Table size in SQL 2005
DECLARE @TableName VARCHAR(100) --For storing values in the cursor
--Cursor to get the name of all user tables from the sysobjects listing
DECLARE tableCursor CURSOR
FOR
select [name]
from dbo.sysobjects
where OBJECTPROPERTY(id, N'IsUserTable') = 1
FOR READ ONLY
--A procedure level temp table to store the results
CREATE TABLE #TempTable
(
tableName varchar(100),
numberofRows varchar(100),
reservedSize varchar(50),
dataSize varchar(50),
indexSize varchar(50),
unusedSize varchar(50)
)
--Open the cursor
OPEN tableCursor
--Get the first table name from the cursor
FETCH NEXT FROM tableCursor INTO @TableName
--Loop until the cursor was not able to fetch
WHILE (@@Fetch_Status >= 0)
BEGIN
--Dump the results of the sp_spaceused query to the temp table
INSERT #TempTable
EXEC sp_spaceused @TableName
--Get the next table name
FETCH NEXT FROM tableCursor INTO @TableName
END
--Get rid of the cursor
CLOSE tableCursor
DEALLOCATE tableCursor
--Select all records so we can use the reults
SELECT * into tbltables_size
FROM #TempTable
--Final cleanup!
DROP TABLE #TempTable
GO
SELECT * from tbltables_size ORDER BY CAST(LEFT(dataSize,LEN(dataSize)-3) AS NUMERIC(18,0)) DESC
--Cursor to get the name of all user tables from the sysobjects listing
DECLARE tableCursor CURSOR
FOR
select [name]
from dbo.sysobjects
where OBJECTPROPERTY(id, N'IsUserTable') = 1
FOR READ ONLY
--A procedure level temp table to store the results
CREATE TABLE #TempTable
(
tableName varchar(100),
numberofRows varchar(100),
reservedSize varchar(50),
dataSize varchar(50),
indexSize varchar(50),
unusedSize varchar(50)
)
--Open the cursor
OPEN tableCursor
--Get the first table name from the cursor
FETCH NEXT FROM tableCursor INTO @TableName
--Loop until the cursor was not able to fetch
WHILE (@@Fetch_Status >= 0)
BEGIN
--Dump the results of the sp_spaceused query to the temp table
INSERT #TempTable
EXEC sp_spaceused @TableName
--Get the next table name
FETCH NEXT FROM tableCursor INTO @TableName
END
--Get rid of the cursor
CLOSE tableCursor
DEALLOCATE tableCursor
--Select all records so we can use the reults
SELECT * into tbltables_size
FROM #TempTable
--Final cleanup!
DROP TABLE #TempTable
GO
SELECT * from tbltables_size ORDER BY CAST(LEFT(dataSize,LEN(dataSize)-3) AS NUMERIC(18,0)) DESC
Tuesday, September 15, 2009
System Requirements for Database Mirroring
· Database mirroring is fully supported on standard, developer and enterprise edition.
· We need the principal and mirror server. Witness is a option in our case we can keep it as manual changeover.
· Operation can be synchronous (obviously adds some latency cost to complete transaction across two servers --- advantages high availability and high protection)
o or asynchronous( more speed – high performance - without waiting for mirror to write on the log)
· sql server instances should be of same service pack
· verify we have the enough disk space where the mdf and ldf is stored on the both the servers
· the principal database should be on FULL database recovery mode
· please note all transactions are written to transaction log and not truncated
· We need the principal and mirror server. Witness is a option in our case we can keep it as manual changeover.
· Operation can be synchronous (obviously adds some latency cost to complete transaction across two servers --- advantages high availability and high protection)
o or asynchronous( more speed – high performance - without waiting for mirror to write on the log)
· sql server instances should be of same service pack
· verify we have the enough disk space where the mdf and ldf is stored on the both the servers
· the principal database should be on FULL database recovery mode
· please note all transactions are written to transaction log and not truncated
Longest running sql query
--sql 2005 & 2008
--to identify the longest running sql query -- run under the master database
SELECT DISTINCT TOP 10
t.TEXT QueryName,
s.execution_count AS ExecutionCount,
s.max_elapsed_time AS MaxElapsedTime,
ISNULL(s.total_elapsed_time / s.execution_count, 0) AS AvgElapsedTime,
s.creation_time AS LogCreatedOn,
ISNULL(s.execution_count / DATEDIFF(s, s.creation_time, GETDATE()), 0) AS FrequencyPerSec
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t
ORDER BY
s.max_elapsed_time DESC
GO
--to identify the longest running sql query -- run under the master database
SELECT DISTINCT TOP 10
t.TEXT QueryName,
s.execution_count AS ExecutionCount,
s.max_elapsed_time AS MaxElapsedTime,
ISNULL(s.total_elapsed_time / s.execution_count, 0) AS AvgElapsedTime,
s.creation_time AS LogCreatedOn,
ISNULL(s.execution_count / DATEDIFF(s, s.creation_time, GETDATE()), 0) AS FrequencyPerSec
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t
ORDER BY
s.max_elapsed_time DESC
GO
Thursday, September 10, 2009
SQL SERVER – TempDB is Full. Move TempDB from one drive to another drive.
If you come across following errors in log file,
Source: MSSQLSERVER
Event ID: 17052
Description: The LOG FILE FOR DATABASE 'tempdb' IS FULL.
Back up the TRANSACTION LOG FOR the DATABASE TO free
up SOME LOG SPACE
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.
Source: MSSQLSERVER
Event ID: 17052
Description: The LOG FILE FOR DATABASE 'tempdb' IS FULL.
Back up the TRANSACTION LOG FOR the DATABASE TO free
up SOME LOG SPACE
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.
System Database
master
Records all of the system level information for a SQL Server system.
It records all login accounts.
Records the existence of all other databases, including the location of the database files.
Records the initialization information for SQL Server;
Always have a recent backup of master available.
tempdb
tempdb holds all temporary tables and temporary stored procedures.
tempdb is a global resource; the temporary tables and stored procedures for all users connected to the system are stored there.
tempdb is re-created every time SQL Server is started so the system starts with a clean copy of the database. Because temporary tables and stored procedures are dropped automatically on disconnect, and no connections are active when the system is shut down, there is never anything in tempdb to be saved from one session of SQL Server to another.
By default, tempdb autogrows while SQL Server is running.
Unlike other databases, however, it is reset to its initial size each time the database engine is started.
If the size defined for tempdb is small, part of your system processing load may be taken up with autogrowing tempdb to the size needed to support your workload each time to restart SQL Server.
You can avoid this overhead by using ALTER DATABASE to increase the size of tempdb.
model
The model database is used as the template for all databases created on a system.
When a CREATE DATABASE statement is issued, the first part of the database is created by copying in the contents of the model database, then the remainder of the new database is filled with empty pages. Because tempdb is created every time SQL Server is started, the model database must always exist on a SQL Server system.
msdb
This is used by SQL Server Agent for scheduling alerts and jobs, and recording operators
Records all of the system level information for a SQL Server system.
It records all login accounts.
Records the existence of all other databases, including the location of the database files.
Records the initialization information for SQL Server;
Always have a recent backup of master available.
tempdb
tempdb holds all temporary tables and temporary stored procedures.
tempdb is a global resource; the temporary tables and stored procedures for all users connected to the system are stored there.
tempdb is re-created every time SQL Server is started so the system starts with a clean copy of the database. Because temporary tables and stored procedures are dropped automatically on disconnect, and no connections are active when the system is shut down, there is never anything in tempdb to be saved from one session of SQL Server to another.
By default, tempdb autogrows while SQL Server is running.
Unlike other databases, however, it is reset to its initial size each time the database engine is started.
If the size defined for tempdb is small, part of your system processing load may be taken up with autogrowing tempdb to the size needed to support your workload each time to restart SQL Server.
You can avoid this overhead by using ALTER DATABASE to increase the size of tempdb.
model
The model database is used as the template for all databases created on a system.
When a CREATE DATABASE statement is issued, the first part of the database is created by copying in the contents of the model database, then the remainder of the new database is filled with empty pages. Because tempdb is created every time SQL Server is started, the model database must always exist on a SQL Server system.
msdb
This is used by SQL Server Agent for scheduling alerts and jobs, and recording operators
Wednesday, September 9, 2009
Converting varchar to numeric for product size comparison
if the productsize is in varchar and needs to be used in the where condition for comparison of sizes it can be converted as below and used.The below case can we used in the where condition
case when productsize = '' or replace(productsize ,'"','') like '%[^0123456789.]%' then 0
else convert(numeric(18,2),replace(productsize ,'"',''))
end >= 1
case when productsize = '' or replace(productsize ,'"','') like '%[^0123456789.]%' then 0
else convert(numeric(18,2),replace(productsize ,'"',''))
end >= 1
Tuesday, September 8, 2009
SQL DB-Mail
1. open sql server managemnt studio
2. Go to Management
3. Go to database mail.
4. Right click --- select configure database mail
5. In the account name type in for example: vijay
6. Under email address enter the email id for example: xxxx@xxx.ie
7. Under reply email address enter the email id for example: xxxx@xxx.ie
8. Under sername name specifity the smtp ip address 192.169.0.x.
9. click next and finish
10. Now click on configure database mail and click on manage profile security.
11. Check the profile name and select "Yes" default profile.
2. Go to Management
3. Go to database mail.
4. Right click --- select configure database mail
5. In the account name type in for example: vijay
6. Under email address enter the email id for example: xxxx@xxx.ie
7. Under reply email address enter the email id for example: xxxx@xxx.ie
8. Under sername name specifity the smtp ip address 192.169.0.x.
9. click next and finish
10. Now click on configure database mail and click on manage profile security.
11. Check the profile name and select "Yes" default profile.
Database Backup
The following are guidelines for database backup.
Based on the data criticality and availability required the backup's can be designed.
Option -1 --- High availability
1. Have a complete hardware backup where the sql data and operating system are backed up with the appropriate raid configuration.
2. Database mirrored with witness for automatic fail over.
3. Full Database backed up every night to external media (drive or tapes)..
4. Differential transaction Database backed up every 15 min.
Option -2 --- High availability
1. System state back up including the operating system.
2. Database mirrored with witness for automatic fail over.
3. Full Database backed up every night to external media (drive or tapes).
4. Differential transaction Database backed up every 15 min.
5. Please make sure master and msdb database backed up.
Based on the data criticality and availability required the backup's can be designed.
Option -1 --- High availability
1. Have a complete hardware backup where the sql data and operating system are backed up with the appropriate raid configuration.
2. Database mirrored with witness for automatic fail over.
3. Full Database backed up every night to external media (drive or tapes)..
4. Differential transaction Database backed up every 15 min.
Option -2 --- High availability
1. System state back up including the operating system.
2. Database mirrored with witness for automatic fail over.
3. Full Database backed up every night to external media (drive or tapes).
4. Differential transaction Database backed up every 15 min.
5. Please make sure master and msdb database backed up.
SQL Tips
SQL Tips
1. Do not use the stored procedure within the DTS jobs.
2. When the server crashed or harddisk crashes, if you do not have msdb backed up the DTS Jobs are lost.
3. Please make sure your master and msdb database are backed up.
4. Also have the procedures written as stored procedures and call them in DTS.
1. Do not use the stored procedure within the DTS jobs.
2. When the server crashed or harddisk crashes, if you do not have msdb backed up the DTS Jobs are lost.
3. Please make sure your master and msdb database are backed up.
4. Also have the procedures written as stored procedures and call them in DTS.
No global profile is configured. Specify a profile name in the @profile_name parameter.
--No global profile is configured. Specify a profile name in the @profile_name parameter.
Solution
1. open sql server managemnt studio
2. Go to Management
3. Go to database mail.
4. Right click --- select configure database mail
5. Select Manage profile security
6. Under defaut profile make sure "Yes" is selected
Solution
1. open sql server managemnt studio
2. Go to Management
3. Go to database mail.
4. Right click --- select configure database mail
5. Select Manage profile security
6. Under defaut profile make sure "Yes" is selected
Thursday, September 3, 2009
Templates SSRS Reports
In an attempt to standardise the look and feel of the SSRS Reports we produce, template for a Portrait and Landscape report can be generated.
The reports can have a ‘Technical Notes’ textbox which is hidden, any stored procedures\need to know info on the report can be stored here, date formatting, page formatting can be stored.
the location to be stored is
Now the reports can be added using the templates.
Subscribe to:
Posts (Atom)