Hot answers tagged

9

Revert the database back to multi user mode and try something like the following. alter database xyz SET single_user with rollback immediate dbcc checkdb('xyz',REPAIR_ALLOW_DATA_LOSS) Likely another process is grabbing the database connection before you get in there. Combining both statements should ensure you get that connection.


6

Something like this may do the trick. I just quickly modified something I use for a slightly different purpose. declare @dbname varchar(80), @lastfull datetime, @fullback varchar(1024), @position int, @SQL nvarchar(max) set @dbname = 'YourDB' select @lastfull = MAX(backup_finish_date) FROM master.sys.databases d LEFT ...


4

I would attach the files to a new database, script the table and re-create it in your active database. First up, move the *.mdf and *.ldf files to the default database file locations. This way, they'll inherit the appropriate file level permissions. Now, create a new database from the existing files, but using a new name: USE master GO CREATE DATABASE ...


3

Different databases use different network protocols. tsql only works with MS SQL and Sybase. That's actually an exception to the general rule, because they historically came from the same codebase. For Postgres, you need the command-line client psql, or (as you mentioned already) the Postgres ODBC driver, or a program build using the Postgres C client ...


3

There are solutions to this problem all over the forum and blog sites @MonkeyMagix. What you need to do is restore the msdb backup as msdb_old (or something similar), then run code against the _old DB to 1) build the code that creates the Agent jobs from the _old DB; http://www.sqlservercentral.com/scripts/generate+jobs+scipts/68105/ or 2) insert the ...


3

In my experience, detach/attach is the fastest method. The bottleneck would probably be how quickly you could copy the files across the network. Assuming the two databases have identical Windows accounts (if you're using SQL accounts you may have to update SIDs), you could probably use something like this script that I have laying around from before I ...


3

Essentially, you're trying to work around what appears to be somebody else's bad implementation. That seems reasonable to me, and sometimes, that's a necessity. If this program is indeed "crashing" when trying to connect to one database but not another, as opposed to, oh, you know, displaying an error message, that's pretty weak. I have a standby solution ...


3

The format for this is listed at the bottom of the syntax section of the web page referenced. <time_period> ::= number[minutes | hours | days | weeks | months] Here are the samples. number[minutes| hours| day| weeks| months] Specifies the time interval used to determine if a report or backup file is old enough to be deleted. number is an integer ...


3

You need to specify the -S switch to provide the instance name for sqlcmd to connect to, and it should be in the format "localhost\sqlexpress" instead of "localhost/sqlexpress".


2

A post on Microsoft's web site suggests that you can work around this problem by disabling query prefetching for the linked server. In the ODBC Data Source Administrator panel on the server, configure your linked server from System DSN. Under the Performance tab, uncheck the box 'Enable pre-fetch of data for queries'. OK, OK. Delete and re-create your ...


2

While you certainly could use techniques like SSH port forwarding to make a remote listening TCP socket appear like a local one, it probably would not be of any help. If your client is "crashing" upon connection, it most likely would not stop doing so just because you are using a different destination IP address. There might be a myriad of reasons why the ...


2

Your query is returning memory used by SQL, not the max server memory it's allowed to consume. How do you get the minimum and maximum memory allocation of a SQL instance using T-SQL? This will show you how to get that value via query. SELECT name, value, value_in_use, [description] FROM sys.configurations WHERE name like '%server memory%' ORDER BY name ...


1

Try something like this based upon the dm_operation_status DMV: while ( select top 1 state_desc from sys.dm_operation_status WHERE resource_type_desc = 'Database' AND major_resource_id = @db AND operation = 'ALTER DATABASE' order by start_time desc ) in ('PENDING', 'IN_PROGRESS') begin print 'waiting for operation to complete: '...


1

You need to encapsulate your column names with square brackets, otherwise SQL Server will interpret the spaces and dashes as part of the statement instead of the column name. SET @sSQL = 'UPDATE #tempProcsAndJobs SET [' + @columnName + '] = 1 WHERE createStatement LIKE ''%' + @columnName + '%'''; EXECUTE (@sSQL); This will result in the following ...


1

Do a query to find all of the children records, which presumably have a key that points back to the parent record. You'll hopefully already have an index on that field. Then just use a similar function to do the copy again, except using the new key for the duplicated parent order instead of the old one.


1

I know this question was asked long ago, but for people Google-ing and come across this post... I assume the query is golden when you run it on the server you are linked to? What is your exact query? Are you doing: Select * from "the linked server" -or- Select * from openquery("the linked server",'Select * from "the table"') There is a difference: ...


1

Does this work: SELECT TOP 1 field1,field2,field3....field15. If yes, how about SELECT TOP 10 field1,field2,field3....field15. If yes, how about SELECT TOP 100 field1,field2,field3....field15. Repeat, etc. Assumming it breaks at some point, add an ORDER BY clause for a column (or set of columns) that are unique. Repeat and adjust the number of rows until ...


1

You should install SQLEXPRADV_x64_ENU.exe or SQLEXPRADV_x86_ENU.exe depending on the architecture which is full version express edition. If you want Management studio, you can download the management studio separately and install it later. Check the following link for more details: http://www.microsoft.com/en-us/download/details.aspx?id=30438


1

MS SQL has built in methods for this. The following stored procedure will give you lots of useful information regarding the schedule, last run and whether it was successful. EXEC msdb.dbo.sp_help_job @Job_name = 'YourMaintenancePlan.NameHere' Also have a look through all the sp_help... system stored procedures in the msdb database. There are some awesome ...


Only top voted, non community-wiki answers of a minimum length are eligible