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