Tag Info

New answers tagged

2

You could use the Windows System Resource Manager (WSRM) feature for this. It is not present in Windows starting from 2012 R2, but since this is an old SQL 2005, I would expect the OS to be old-ish as well. I've never used it myself, but, though a bit complicated, it might do the trick for you.


0

Calculation is something which you need to analyse an figure out with some calculation on basis of that. Kindly go through the below discussion which will help you out in deciding what values you can assign: What is a deterministic method for evaluating a sensible buffer pool size? Also, would recommend to go through below article for indepth explanation: ...


0

This PowerShell script will list jobs on a local or remote SQL Server, along with a human readable interpretation of their schedule. It uses internal SSMS code to format the schedule, so you must have it installed on the machine you run the script on. #requires -modules sqlps #requires -version 3 param([string]$ServerToQuery = ([Environment]::MachineName), ...


-2

Try it. it resolved timeout expired problem while large size db. Private Sub Command1_Click() On Error Resume Next Dim con As New Connection Dim tm As String con.CommandTimeout = 500'''Command timeout should be 500 With con .ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=dbiBMS;Data ...


0

But, I myself checked the Delete and Insert vs Update on a table that has 30million (3crore) records. This table has one clustered unique composite key and 3 Nonclustered keys. For Delete & Insert, it took 9 min. For Update it took 55 min. There is only one column that was updated in each row. So, I request you people to not guess. The equations will ...


0

In one of our processes we are using Powershell to achieve this.. I am not sure of the link who has written, since it resides in our process, but below script might be useful to you. param([String]$ServerListPath) #Load the input file into an Object array $ServerNameList = get-content -path $ServerListPath #Load the SQL Server SMO Assemly ...


2

I use Bill Graziano's Script SQL Server Configuration project on Codeplex for this. It can script the whole configuration of a SQL Server instance to .sql files, including SQL Server Agent jobs. It is a command line utility, so with multiple servers to script, you can use a batch or PowerShell script to loop through all the servers.


6

There are two possible ways 1. Automatically create scripts for all SQL Server Jobs I. Open Object Details II. Select All Jobs (Shift + Mouse Click) -> Right Click -> Script Job as


0

This works for me: USE [master] GO ALTER DATABASE [YourDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO USE [master] GO /****** Object: Database [YourDB] Script Date: 11/29/2013 13:40:36 ******/ DROP DATABASE [YourDB] GO


3

VSS is being used to backup your database. Edit: [Thanks Colin] This causes the logged 'I/O is frozen' message. It is not serious. No need to resolve it. Be warned, your database restore chain may be impacted, in which case it is serious. If you backup strategy is weekly full, daily diff, hourly tran per day - 8am -5pm; a daily snapshot VSS backup will ...


0

I know this is an old thread but I would say to a large degree snapshot isolation is a magic bullet. It will eliminate all of your blocking between readers and writers. It will however not prevent writers from blocking other writers. There is no way around that. In my experience, the additional load on the TEMPDB is negligible and the benefits of row ...


3

Since your problem is a conflict between constant updates and being able to run reports without facing deadlocks, I would recommend you consider using READ_COMMITTED_SNAPSHOT isolation set ON. This also has overhead, but usually prevents most deadlocking situations. (Nothing is magic, of course.) You should read the details at: ...


1

After doing some online search,I found what you needed,hope my answer helps you. Answer 1 Note: It Only works with transactional and transactional peer to peer replication T-SQL script which you can use to monitor the status of transactional replication and performance of publications and subscriptions. Things to be considered before executing the ...


2

To get the information for a single instance you would use DaysUntilExpiration value for the LOGINPROPERTY function. This will give you the days until the password expires. You can then cast that as an integer and use the DATEADD function to get the actual date. SELECT name AS LoginName, DATEADD(DAY, CAST(LOGINPROPERTY(name, 'DaysUntilExpiration') AS ...


0

Hello Guys after lot of research and after testing on one of my servers i used the below query with slight modifications and gives perfect results, Hope this query would be useful to fetch LS status report. USE [master] GO begin set nocount on DECLARE @Recipients varchar(275) DECLARE @Subject varchar(275) DECLARE @Body varchar(MAX) ...


6

I'm guessing here based on how I'd implement MIRROR TO: Probably, SQL Server streams the backup data to two backup devices if you use MIRROR TO. This means that every block they read is written two times. The data stream has a fork. What they are certainly not doing is write the data once and then copy it over. That would a) incur an additional read pass ...


3

The script below will give you most of the information from the standard reports in SSMS when executed on your secondary server. select ls.primary_server,ls.primary_database,lsd.restore_delay, DATEDIFF(mi,lms.last_restored_date,getdate()) as time_since_last_restore, lms.last_copied_date,lms.last_restored_date,lms.last_copied_file, lms.last_restored_file, ...



Top 50 recent answers are included