Temporary table dropping

IF EXISTS
(
SELECT *
FROM tempdb.dbo.sysobjects
WHERE ID = OBJECT_ID(N'tempdb..#temp1')
)
BEGIN
drop table #temp1
END

SQL Reports Parameters

Report Parameters

Next week From date :

=CDate(FormatDateTime(DateAdd("ww",1,DateAdd("d",-(DatePart("w",NOW)-2),NOW)),2))

Next week To date:

=CDate(FormatDateTime(DateAdd("ww",1, DateAdd("d",4, DateAdd("d",-(DatePart("w",NOW)-2),NOW) )) ,2))

Current week From date:

=CDate(FormatDateTime(DateAdd("d",-(DatePart("w",NOW)-2),NOW),2))

Current week to date:

=CDate(FormatDateTime(DateAdd("d",4,DateAdd("d",-(DatePart("w",NOW)-2),NOW)),2))

access to linked server

Linked server syntax
insert into [xxx.xxx.x.xx].bmsus.dbo.bimsus_AccountsReceivable_Outbound_Data
select * from bimsus_AccountsReceivable_Outbound_Data

SQL Large log file size

There are instance where the log files have grown up too large for example 200 gb of file size, in that case, here are the following steps to be done to reduce the log file.
1.Check the database options if it is simple change it to Full.
2. Backup the database using the below script.
--****************************************************
--Backup database script
--****************************************************
BACKUP DATABASE [lmkdw] TO
DISK = N'd:\lmkdw_backup.bak' WITH NOFORMAT,
INIT, NAME = N'lmkdw_backup', SKIP, REWIND, NOUNLOAD,
STATS = 10
3. Then backup the log file using the below script
--****************************************************
--Backup database script
--****************************************************
BACKUP LOG [apacdw] TO DISK = N'd:\lmkdw.trn'
WITH NOFORMAT, INIT, NAME = N'lmkdw-Transaction Log Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
go
4. Now shrink the log file using the below script
DBCC SHRINKFILE (N'lmkdw_Log' , 786)
5. Finall turn the database options back to simple or based on your needs.

Robocopy Tool

Robocopy is a awesome tool to copy backup files. Here is the syntax to copy files.
robocopy "Z:\DATABASE\FULLBACKUP" "\\xxx.xxx.x.xxx\backups\FullBackup" /zb /copyall /purge /MIR

robocopy "source" "destination"

Copy options :
/S :: copy Subdirectories, but not empty ones./E :: copy subdirectories, including Empty ones./LEV:n :: only copy the top n LEVels of the source directory tree.
/Z :: copy files in restartable mode./B :: copy files in Backup mode./ZB :: use restartable mode; if access denied use Backup mode./EFSRAW :: copy all encrypted files in EFS RAW mode.
/COPY:copyflag[s] :: what to COPY for files (default is /COPY:DAT).(copyflags : D=Data, A=Attributes, T=Timestamps).(S=Security=NTFS ACLs, O=Owner info, U=aUditing info).
/DCOPY:T :: COPY Directory Timestamps.
/SEC :: copy files with SECurity (equivalent to /COPY:DATS)./COPYALL :: COPY ALL file info (equivalent to /COPY:DATSOU)./NOCOPY :: COPY NO file info (useful with /PURGE).
/SECFIX :: FIX file SECurity on all files, even skipped files./TIMFIX :: FIX file TIMes on all files, even skipped files.
/PURGE :: delete dest files/dirs that no longer exist in source./MIR :: MIRror a directory tree (equivalent to /E plus /PURGE).
/MOV :: MOVe files (delete from source after copying)./MOVE :: MOVE files AND dirs (delete from source after copying).

Copying folders, subfolders and files

Copying folders, subfolders and files from one location to another and over writing it.Very useful for backing up of files.

Const OverWriteFiles = TrueSet objFSO = CreateObject("Scripting.FileSystemObject")objFSO.CopyFolder "c:\vijay\t1" , "c:\vijay\t2" , OverWriteFiles
set objfso = nothing

set date field null

to set the date field null
update table name set date = null

VB Script file to delete backup files

Create vbs file Vbscript file with the below code to delete backup files which are older than 3 days


Dim FsoDim Directory
Dim Modified
Dim Files Set Fso = CreateObject("Scripting.FileSystemObject")
Set Directory = Fso.GetFolder("E:\transfer\TulsaBackup\goes")
Set Files = Directory.Files
For Each Modified in FilesIf DateDiff("D", Modified.DateLastModified, Now) > 3 Then Modified.Delete
Next

VBScript to copy files across

Script to copy the backup files form one location to another. This is very useful to copy the back and diff back up files across the network as well.This script checks if the file not exists and then copies it.

The below script can run on a sql job, as activex script ( vbscript)

Dim sOriginFolder, sDestinationFolder, sFile,oFSO
Set oFSO = CreateObject("Scripting.FileSystemObject")
sOriginFolder = "G:\GOES DB Backup\Diff\ManufacturingTulsa"sDestinationFolder = "\\192.xxx.x.xxx\transfer\tulsabackup\ManufacturingTulsa\Diff"
For Each sFile In oFSO.GetFolder(sOriginFolder).Files If Not oFSO.FileExists(sDestinationFolder & "\" & oFSO.GetFileName(sFile)) Then
oFSO.GetFile(sFile).Copy sDestinationFolder & "\" & oFSO.GetFileName(sFile),True
End If Next
set oFSO = Nothing

Cannot delete the Distributor Database

I was working on the Replication on one of my production database, to have high availability in our europe locations, as the production database was in the US. The replication I tried out was Transaction replication. Before the replication I tried out the log shipping, and had the recovery model on Full, due to this the log file had grown to 456 GB.

Leaving the recovery mode on Full and working on the transaction replication is not advisable. So I had to delete the replication, I was able to delete the subscription, publication but not the distributor.

The distributor database was huge in size i.e., 456 GB. I could not delete this, the message was it is used by replication.

So to delete the distributor database --- to to management --- activity monitor --- view processes and kill any process running.

Then using sp_dropdistributor you can drop the database or click on replication on the database engine and click on disable publishing and distribution

SQL DTC error

error "OLE DB provider SQLOLEDB was unable to begin a distributed transaction"

This error may occur when use begin and commit transction when you are using linked server. In the case go to control panel --- administrator tools --- components --- click on Local DTC ---
properties and check Allow remote clients

check allow remote administration
check allow inbound
check allow outbound

Further to this you need to have a windows account which is authenticated on both the servers.

SQL Log space utilization

--Gives the log space utilization of every database

DBCC SQLPERF(LOGSPACE);
GO

Max size of Table, Rows and byte

This query is very useful to find out the tables which has max row count, column count and byte utilized


USE DatabaseName
GO
CREATE TABLE #temp (
table_name sysname ,
row_count INT,
reserved_size VARCHAR(50),
data_size VARCHAR(50),
index_size VARCHAR(50),
unused_size VARCHAR(50))
SET NOCOUNT ON
INSERT #temp
EXEC sp_msforeachtable 'sp_spaceused ''?'''
SELECT a.table_name,
a.row_count,
COUNT(*) AS col_count,
a.data_size
FROM #temp a
INNER JOIN information_schema.columns b
ON a.table_name collate database_default
= b.table_name collate database_default
GROUP BY a.table_name, a.row_count, a.data_size
ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC
DROP TABLE #temp

High Availability

Failover clustering and database mirroring both provide the following:
Automatic detection and failover
Manual failover
Transparent client redirect
Failover clustering has the following constraints:
Operates at the server instance scope
Requires signed hardware
Has no reporting on standby
Utilizes a single copy of the database
Does not protect against disk failure

Database mirroring offers the following benefits:
Uses a single, duplicate copy of the database
Note: If you require additional copies, you can use log shipping on the database in addition to database mirroring.
Uses standard servers
Provides limited reporting on the mirror server by using database snapshots.
When it operates synchronously, provides for zero work loss through delayed commit on the principal database.

Database mirroring offers a substantive increase in availability over the level previously possible with SQL Server and offers an easy-to-manage alternative to failover clustering.
Asynchronous database mirroring is Not supported on standard edition. Asynchronous is only supported in Enterprise Version.

Log shipping

Log shipping can be a supplement or an alternative to database mirroring. Although similar in concept, asynchronous database mirroring and log shipping have key differences. Log shipping offers the following distinct capabilities:

Supports multiple secondary databases on multiple server instances for a single primary database.

Allows a user-specified delay between when the primary server backs up the log of the primary database and when the secondary servers must restore the log backup. A longer delay can be useful, for example, if data is accidentally changed on the primary database. If the accidental change is noticed quickly, a delay can let you retrieve still unchanged data from a secondary database before the change is reflected there.
Asynchronous database mirroring has the potential advantage over log shipping of a shorter time between when a given change is made in the primary database and when that change is reflected to the mirror database.
An advantage of database mirroring over log shipping is that high-safety mode is a no data loss configuration that is supported as a simple failover strategy.

Note:
For information about how to use log shipping with database mirroring, see Database Mirroring and Log Shipping.



Replication - Replication offers the following benefits:

Allows filtering in the database to provide a subset of data at the secondary databases because it operates at the database scope

Allows more than one redundant copy of the database

Allows real-time availability and scalability across multiple databases, supporting partitioned updates

Allows complete availability of the secondary databases for reporting or other functions, without query recovery.

Transaction Log Size

The transaction log size gets increased to the size of the disk, if the log file is not shrunk. For example if you see a transaction log file of size 274 GB, which is in my case as consumed all the disk space in the server.

The following need to be checked.

The recovery model option right click database properties - options - is this set to FULL or Simple.

If this set to FULL, then the log
In the full and bulk-logged recovery models, a sequence of transaction log backups is being maintained. The part of the logical log before the MinLSN (Log sequnce number) cannot be truncated until those log records have been copied to a log backup.

This means the following script need to be run

--back up the transaction log file
use Manufacturingarchive
BACKUP LOG [Manufacturingarchive] TO DISK = N'E:\Manufacturing.trn' WITH NOFORMAT, NOINIT,
NAME = N'ManufacturingArchive-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

--shrink the log file
go
use Manufacturingarchive
DBCC SHRINKFILE (N'MANUFACTURINGARCHIVE_Log' , 7)

--shrink the data file
use Manufacturingarchive
DBCC SHRINKFILE (N'MANUFACTURINGArchive_Data' , 786)

exec sp_helpfile

Now how do I reclaim the space in the server is the question?

The above query reclaims the logical space, but to relaim the physical space on the change the recovery model option --- right click database --- properties --- options --- to Simple then run
use manufacturingarchive
exec sp_helpfile

dbcc shrinkfile (MANUFACTURINGARCHIVE_Log,100,TRUNCATEONLY)

The above recove the space on the server.

SQL Server space issue

I had situation here with one of our affiliate sql server crashed in USA, we had integrations running between our local (Ireland) and USA server, since the usa server crashed the Ireland server started to slow down with no reason.

Having looked at the log evertime the integrations failed we had log files building up. In the following folders the log files can be deleted.

i. C:\Documents and Settings --- this had log files and pc health cab files
ii. C:\WINDOWS\Temp
iii. C:\WINDOWS\system32\LogFiles
iv. C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG
v. c:\program Files\Microsoft SQL Server\MSSQL.2\OLAP\Log
vi. C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\LogFiles

Tables to clear when space is an issue

delete from dbo.LMK_POP10100_PURCHASEORDER_HDR where docdate < '2010-01-10'
delete from dbo.LMK_POP10110_PURCHASEORDER_DET where reqdate <= '2010-01-01'
delete from dbo.LMK_AR_Errors_Data_To_GP
delete from dbo.LMK_BSB10100_OUTBOUND_Errors
delete from dbo.LMK_Mat_Issues_Errors_Data_To_GP
delete from dbo.LMK_Payables_Credit_Errors_Data_To_GP
delete from dbo.LMK_Payables_Errors_Data_To_GP
delete from dbo.LMS_GP_MFG_CustomerMaster_error
delete from dbo.BV_AR_Errors_Data_To_GP
delete from dbo.BV_Payables_Credit_Errors_Data_To_GP
delete from dbo.BV_Payables_Errors_Data_To_GP

SQL 2005 PAGE FILE BIG

Today we had a page file size issue, due to this the memory utilization was 16 GB, this stop all the integrations and giving out time out errors, the following actions where taken to resolve this.

1. Restart the sql server.
2. Check the database size.
3. Shrink the database mdb and log file size using the following scripts.
4. Check the max table size.

use manufacturing
exec sp_helpfile
dbcc shrinkfile (Manufacturing_Data)

SQL Database on Suspect Mode

We had a scenario in our US office where the Sql server was abruptly stopped due to power outage.

This has caused the database to go on SUSPECT Mode.

To get the database back on line the following sql scripts can be used.

The below script is used to check the database.

DBCC CHECKDB ('ManufacturingTulsa') WITH NO_INFOMSGS,

ALL_ERRORMSGS

The below script is used bring the database on line.

EXEC sp_resetstatus 'ManufacturingTulsa';

ALTER DATABASE ManufacturingTulsa SET EMERGENCY

DBCC checkdb('ManufacturingTulsa')

ALTER DATABASE ManufacturingTulsa SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CheckDB ('ManufacturingTulsa', REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE ManufacturingTulsa SET MULTI_USER

SQL Log Shipping

SQL Log Shipping is an automated process of backingup, copying and restoring the transactionlog from one database from a primary server to one or more secondary server.

Using Log shipping you can frequently synchronize the copy of the database with the original , so this can be used as warm standy by database for high availability.

I would see this as another alternative of database mirroring where asynchronous mirroring is only available in the sql enterprise edition.

SQL Server 2005 Log Shipping

Log Shipping
As I have tried a lot of combinations in to find out an effective method for implementing Transaction Log Shipping between servers.

Here's the steps that I finally found that works. Let's assume there are 2 servers with SQL Server 2005. Make sure both servers have latest SP.

In this example I am going to explain the log shipping between two servers, connected through vpn, server A location Europe and server B location USA.

1. Make sure you have created same user accounts with full permissions on both servers, if you are the DBA sa is preferred.

2. Make sure File Sharing is enabled on the local area connection between the server. Also enable file sharing in Firewall.

3. Make sure the local network connection is not regular LAN. Recommended be a gigabit card with near zero data corruption. Both cable and switch needs to be perfect. If possible, connect both servers using Fibre optic cable directly on the NIC in order to avoid a separate Switch.

4. Now create a folder named sqldata on both servers. Let's assume the folder is on E:\Transfer.

5. On Primary Database server, share the folder sqldata and allow windows account Full Access to it. Then allow windows account FullAccess on TranLogs folder. So you are setting the same permission from both "Sharing" tab and from "Security" tab.

6. On Secondary database server, allow windows account Full Access right on Transfer folder. No need to share it.

7. Test whether windows Account ( in our case sa account) can really connect between the servers.

8. The sql account needs to be set up with full access permission. In the example below syncaccount is given as an example.





































9. Now connect the secondary server in the primary server in the ssms and make sure the connectivity is working fine.

10. Now backup the database from the primary database say test.bak and the transaction log back up as test.trn.

11. Copy the file the test.back and the test.trn to the secondary server and restore the database test on the secondary database. please see image below




















































































12. Once you restore the test database in the secondary server the database will be in read only mode, but the advantage is this database can be accesses through sql query analyzer, where as in mirroring this option is not available.

13. Now let us come back to the primary server, right click on the test database and click properties --- transaction log shipping.

14. Click on the check box Enable this as a primary database in a log shipping configuration.




15. Click on back up settings and specify the back up of the transaction log files to be backup, please note this can be a network folder or you local folder. So if you need to do on the local folder say for example f:\vijay\ .... as shown in the example below.







16. Now we have the transaction log files copies on this location.



17. Next step is to add the secondary server, click on add, connect to the secondary server, as we have already set up the test database on the secondary server click on No the secondary database is initialized.

18. Now click on the copy files tab and enter the location of the secondary server file location where the transactin log files will be copied. Please note the user should have full access to this location with read, write permissions.

19 . Next step is to click on the Restore Transaction Log tab and check the Standby Mode and check the Disconnet the users in the database when restoring backups.

20. Now go back to the secondary server and check if the following sql jobs are created and they are running fine. I have xxx just to remove by company name.

LSRestore_xxx-HOST\GOES_test
LSCopy_xxx-HOST\GOES_test


I hope this helps in setting up the sql server 2005 log shipping.

Moving SQL Database Files mdf and ldf

--check the location of the files

Use databasename

GoExec sp_helpfile

--detach the database name
Exec sp_detach_db 'databasename'

--copy the mdf and ldf files to the location

--attach the databasename

Exec sp_attach_db 'databasename',

'D:\sqldata\GPSBSBBVDat.mdf',
'G:\sqldata\GPSBSBBVLog.ldf'

SQL Having clause....

--Having clause

select name,sum(dpgrossbookingpriceineuro) from vtgeneral
where bookdate >= '2008-07-01' and bookdate <= '2009-06-30'
group by name
having sum(dpgrossbookingpriceineuro) > 50000
order by sum(dpgrossbookingpriceineuro) desc

select name,sum(dpgrossbookingpriceineuro) from vtgeneral
where bookdate >= '2008-07-01' and bookdate <= '2009-06-30'
group by name
having sum(dpgrossbookingpriceineuro) < 5000
order by sum(dpgrossbookingpriceineuro) desc

SQL Performance and Optimization

The sql server performance and optimization can be improved in many ways, but it again depends on the number of factors and individual scenariso, here are some the of steps taken by myself.

1. Check the size of the tables, you can use the query from my post which identifies the largest table.

2. Check the max. cpu utilization, you can use the query from my post on this. Then you can work on the sql query.

3. Check the indexes on the table, see if the tables have clustered index and non clustered index, it depends on how the table is queried and what fields are queried.

4.Location of the mdf and ldf files, it is advisable for optimimum performance to locate the temp database file on d:/ drive, mdf on the e:/ and the ldf on the f:/ and leave the operating system on the c:/.

5. Check the views on the database this may used in excess, too many views causes concern on the performance of the database and on the sql server.

Identify the No of days between job status

The above table represents the status of a jobnumber from order entery to completion. In this case for performance mprovement if we need to identify the number of days it has taken between each status

drop table #temp
delete from tbltulsaopkpi
create table #temp( id int identity , lotno varchar(30), jobDate datetime, status varchar(5) )
insert into #temp
select jobnumber, date,jobstatus
from tbltransjobstatus where jobnumber = 'A8000160-1'
--select * from #temp
declare @ctr int, @jobstatus varchar (3), @lotno varchar(20)
SELECT @ctr = count(id) FROM #temp
declare @olddate datetime, @newDate datetime
declare @i as int, @noDays as int
set @i= 1
set @noDays = 0
WHILE @i <= @ctr -- - 1 >= 0
BEGIN
if @i = 1 --or @i =@ctr
BEGIN
select @oldDate = jobdate, @lotNo = lotno, @jobStatus = status
from #temp
where id = @i
-- select @oldDate, @lotNo, @jobStatus, 0
insert into tbltulsaopkpi values
(@lotno,@jobstatus,0)
END
else
BEGIN
select @newDate = jobdate, @lotNo = lotno, @jobStatus = status
from #temp
where id = @i
set @noDays = datediff(day,@oldDate, @newDate)
- (datediff(ww,@oldDate, @newDate + case when datename(dw,@oldDate) = 'sunday' then 1 else 0 end))
- (datediff(ww,@oldDate, @newDate + case when datename(dw,@newDate) = 'saturday' then 1 else 0 end))
- (datediff(ww,@oldDate, @newDate + case when @newDate not in (select date from tblholiday where finyr = dbo.ufgetfinyr(@newDate)) then 1 else 0 end))
insert into tbltulsaopkpi values
(@lotno,@jobstatus,@noDays)
-- select @newDate, @lotNo, @jobStatus, @noDays
set @oldDate = @newDate
END
set @i = @i + 1
-- create table tbltulsaopkpi (
-- lotno varchar(20),
-- jobstatus varchar(5),
-- NoofDays int
-- )
END
-- select * from tbltulsaopkpi

--writter by marquerite

SQL Restore from Backups

Please note to have this working the BACKUP has to be done first, then from that point the transaction log has to be done. If there a backup done in between this process the RESTORE may not work properly.


Sql script to RESTORE DATABASE, please note this can also be done through wizards in SSMS.

RESTORE DATABASE [test]
FROM DISK = 'E:\transfer\vijay\test_backup_201001111451.bak'
WITH FILE = 1, NORECOVERY,
MOVE N'test' TO N'E:\MSSQL\test.mdf',
MOVE N'test_Log' TO N'F:\MSSQL\test_log.ldf',
NOUNLOAD, STATS = 10

Sql script to RESTORE TRANSACTION LOG files, this is applicable when you do the full back up and transaction log back ups. This option enables to get to the point of data loss.

RESTORE LOG [test]
FROM DISK = 'E:\Transfer\vijay\test.trn'
WITH FILE = 1,
RECOVERY

SQL Backup

Sql backup

You can do a complete full backup on daily basis in the night and subsequently do a transaction log backup for every hour or every two hours

The next option is to do a set the options to simple and do a simple backup daily then do a transaction back up every two hours.



-*********************************************************************************
--BACKUP DATABASE
--*********************************************************************************
BACKUP DATABASE [Pacop] TO
DISK = N'Z:\DATABASE\Pacop_backup.bak' WITH NOFORMAT,
INIT, NAME = N'Pacop_backup', SKIP, REWIND, NOUNLOAD,
STATS = 10
--*********************************************************************************
--BACKUP LOG OVERWRITE THE TRN LOG FILE
--*********************************************************************************
BACKUP LOG [Pacop] TO DISK = N'Z:\DATABASE\TRANSACTION LOG BACKUPS\Pacop.trn'
WITH NOFORMAT, INIT, NAME = N'Pacop-Transaction Log Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
go
DBCC SHRINKFILE (N'Pacop_Log' , 786)
--*********************************************************************************

--*********************************************************************************
--BACKUP LOG APPEND THE TRN LOG FILE
--*********************************************************************************
BACKUP LOG [Pacop] TO DISK = N'Z:\DATABASE\TRANSACTION LOG BACKUPS\Pacop.trn'
WITH NOFORMAT, NOINIT, NAME = N'Pacop-Transaction Log Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

--*********************************************************************************
--*********************************************************************************
DBCC SHRINKFILE (N'Pacop_Log' , 786)
--*********************************************************************************