Questions tagged [sql-server-2005]

SQL Server 2005 (major build version 9.00.xxxx). Please also tag sql-server.

Filter by
Sorted by
Tagged with
3
votes
1answer
101 views

What if all the non clustered indexes on my table were filtered indexes?

I have a table that is used heavily. This table has 370,370 records. When I run the following select: select [ImageSize] , the_num = count(*) from [dbo].[ProductImages] group by [ImageSize] order ...
0
votes
2answers
74 views

Materialized view in SQL Server

Have a bit of glitches from the time I was using SQL Server back in 2006 and the many other databases I played with (and which I have not touched since then). I need to create a materialized view to ...
4
votes
2answers
5k views

sqloledb vs sqlncli - Performance difference or just feature set?

I've been running through some Classic ASP code recently and was wondering if there was a performance difference between connecting using: Provider=SQLOLEDB;server=NTSERVERNAME\SQLEXPRESS;database=...
2
votes
1answer
97 views

Page Splits on an empty table

I have a monitoring tool that alerted me to high page splits, 50% of new allocations. After some Google Fu, I found the following query to look and see: SELECT IOS.index_id, O.NAME ...
0
votes
3answers
220 views

when were the sys-dm_os_wait_stats last cleared? sql-2005!

I intend to monitor the execution of my stored procedures in a specific database, using the following script, which I got from How can I monitor the call count per stored procedure per timespan? ...
13
votes
7answers
44k views

Taking backup of all the databases in SQL Server

I have a Microsoft SQL Server 2005 DB server. In the DB server, I have around 250 user databases. I have to take a back up of all these databases. Since, manually taking backup consumes lot of time, I ...
0
votes
0answers
17 views

MSSQL Mirai Botnet, cleaned but connects again using ANOYNMOUS LOGON

well this problem is making me go nuts my server was hacked with the Mirai Botnet, we cleaned the system, Disabled the Server Agent completely and the system is stable and clean BUT... if i re-open ...
2
votes
1answer
261 views

SSRS ReportServer_XXX.log is giving incorrect CPU count

I am currently investigating an issue wherein sometimes the SSRS just suddenly stops accepting requests until we do an IISRESET command. This happens after we added an additional processor on the ...
2
votes
2answers
1k views

PowerShell to get database schemas

I am able to connect to a remote SQL Server with the instance name using PowerShell. I am using an XML file (something like a configuration file) which includes all my database details. PowerShell ...
52
votes
3answers
4k views

Huge slowdown to SQL Server query on adding wildcard (or top)

I've got a zoo of 20 million animals which I track on my SQL Server 2005 database. About 1% of them are black and about 1% of them are swans. I wanted to get details of all the black swans and so, not ...
6
votes
1answer
5k views

How do these snapshot isolation level configurations interact on a SQL Server 2005 instance?

I started troubleshooting a tempdb issue we are having on SQL Server 2005 Enterprise Edition. A developer is getting an error that tempdb is out of space. Technically, the error is: Transaction ...
1
vote
2answers
93 views

SQL 2005 find duplicate indexes

I've created the following script to pull up duplicate indexes on my DB but I need to find a way to implement it across all the databases on the SQL server - any suggestions?` with indexcols as ( ...
9
votes
3answers
2k views

Is there a way to programmatically script all objects associated with a given table?

I know that in SQL management studio I can right click a table/trigger/key and script object as.... Is there a way to do this programmatically, given an object's name? If so, is there a way to find ...
9
votes
1answer
36k views

Query that displays backup information (successful and failed) SQL Server

I have two jobs that backs up two different databases. Job 1 backs up DB1 Job 2 backs up DB2 DB1 fails to back up due to low space on Drive 1 resulting to a Job 1 failure. To fix that issue, I just ...
4
votes
1answer
1k views

sys.sysprocesses versus DMVs

I've seen a couple of blog posts where fairly knowledgeable sounding MVPs are still using sys.sysprocesses rather than the recommended DMVs: sys.dm_exec_connections, sys.dm_exec_sessions and sys....
20
votes
2answers
4k views

Can foreign keys cause deadlocks and hinder READ COMMITTED SNAPSHOT?

This is a followup question from: https://stackoverflow.com/questions/7684477/is-it-possible-to-set-transaction-isolation-level-snapshot-automatically I'm still having deadlock/timeout situations in ...
12
votes
1answer
6k views

SQL Server TempDB behaviour in large memory environment

Reading over this question reminded me of a question I had a while ago. We have a SQL Server that has 512GB of RAM, the main database is 450 GB. We see quite a lot of action in TempDB (ok, I think ...
7
votes
2answers
1k views

Why does this cursor produce results in the incorrect order?

I am writing some dynamic SQL to identify and, perhaps if I'm feeling crazy enough, automatically convert my NONCLUSTERED indexes into CLUSTERED indexes. The line ORDER BY 1,2,3 DESC; in the SQL ...
3
votes
1answer
984 views

How do I understand this deadlock?

I have two threads doing inserts into a table at the same time, causing a deadlock. The interaction with the table is in a new transaction, so I'm pretty confident there is nothing else going on here. ...
6
votes
2answers
14k views

Issues with TempDB mdf file ever increasing

I have a tempdb growth issue. Let me preface everything by giving my tempdb settings. Even with no queries running on the database/server tempdb keeps on increasing in size, at first rapidly and then ...
4
votes
1answer
128 views

How can I find out which statistics are evaluated by the optimizer?

My enterprise has a farm of many nearly-identical, isolated production environments. Each environment has a SQL Server 2005 database server, each supporting a database with nearly identical schemas. ...
-4
votes
1answer
10k views

Unable to open SQL Server database file (.mdf) [closed]

I have a SQL Server 2005 .mdf file that I am trying to open in SQL Server Management Studio so I can add a field to a table, but I am unable to open the file. I can't say that I really understand how ...
6
votes
1answer
9k views

Find transactions that are filling up the version store

we have enabled the "READ_COMMITTED_SNAPSHOT" for some of our SQL Server 2005 databases. Now from time to time we see that our TempDB is filling up the harddisk and we suspect the version store to be ...
1
vote
1answer
935 views

Need to query records based on Date only (Sample Query given)

sSQL = " SELECT ordID, ordName, ordDate, ordShipping, ordStateTax, ordHandling, ordTotal, ordDiscount, ordPrivateStatus FROM orders WHERE (ordStatus = 7) " I want to display orders that ...
1
vote
1answer
2k views

how to get last date of today in datetime datatype?

select dateadd(s, -1, dateadd(month, datediff(month, 0, getdate())+1,0)) when i execute this query, it gives '2012-01-30 23:59:59.000'. I donno how to get it as '2012-01-30 23:59:59.997'. If anybody ...
4
votes
3answers
2k views

How to identify connections using implicit transactions?

I'm investigating a blocking issue that I believe is related to implicit transactions not being committed or rolled back. How can I identify connections that have turned the implicit transactions ...
70
votes
4answers
171k views

How to identify which query is filling up the tempdb transaction log?

I would like to know how to identify the exact query or stored proc which is actually filling up the transactional log of TEMPDB database.
0
votes
1answer
78 views

How do I perform daily, monthly SQL Server 2005 maintenance?

Team, I have SQL Server 2005 and I am new to DBA role and wondering if there is any T-SQL Script/Stored Procedure out there that they will help me accomplish the following on regular basis - Daily, ...
3
votes
1answer
855 views

Loading SQL Server from SQLite with image data

I am trying to load a SQL Server database table from a SQLite database. I have been unable to transfer the data between the two databases since there are: Difficulties in accessing the data in the ...
14
votes
2answers
12k views

Can I query a tab-delimited file from SSMS?

Is it possible to query a tab-delimited file from Sql Server Management Studio to view its data without saving it anywhere? I know you can BULK INSERT from a tab-delimited file using something like: ...
2
votes
4answers
3k views

Find old linked servers on a restored copy of master/msdb db

We moved our dbs to a new server and the drives on the old server are still available, but the SQL service is offline and cannot be started. I want to find the linked servers that were registered on ...
0
votes
1answer
45 views

How to make date intervals from a list of date

How to make date intervals from a list of date (Group by columns A and C, the date range consists of a continuous date sequence):
1
vote
0answers
625 views

What are the risks if you rename a server that hosts a named instance of SQL Server (2005) and skip updating the system metadata?

My apologies as this is the last SQL 2005 instance we have and it's pretty urgent or I wouldn't go through all this trouble asking. Questions What is the risk to leave things as they currently are, ...
1
vote
1answer
45 views

Update statistics

For the data retention purpose I am deleting records from some 60 tables. Some of the tables have less number of records while some have more. I don't want to update the stats for all the tables. Is ...
11
votes
7answers
49k views

How to find all positions of a string within another string

How can I find all the positions with patindex in a table or variable? declare @name nvarchar(max) set @name ='ali reza dar yek shabe barani ba yek ' + 'dokhtare khoshkel be disco raft va ali ...
1
vote
1answer
26 views

script insert SQL Server 2005 from SQL Server 2014 Express

I have a database in SQL Server 2014 Express, and I want to import it to SQL Server 2005. I have generated the scripts to generate the database and to insert the data. All the script generated to be ...
31
votes
11answers
171k views

How can I get the correct offset between UTC and local times for a date that is before or after DST?

I currently use the following to get a local datetime from a UTC datetime: SET @offset = DateDiff(minute, GetUTCDate(), GetDate()) SET @localDateTime = DateAdd(minute, @offset, @utcDateTime) My ...
34
votes
3answers
54k views

What is the difference between Shrink Database and File?

DBCC ShrinkDatabase() DBCC ShrinkFile() Do I need to run both DBCC commands in order to shrink the database? What is the difference between these two above?
2
votes
4answers
4k views

How do I automatically deliver the SQL Server built in reports?

When you right click on a number of items in the object explorer, it gives you the option for a reports menu. Various reports may exist depending on which type of item is selected. Yet, there does not ...
2
votes
1answer
23k views

how to insert a list of varchar values, into a single column table?

In SQL Server 2005: CREATE TABLE __RADHE ( itemNo varchar(10) not null primary key clustered ); GO INSERT INTO __RADHE ??? '34926840', '34927020', '34927202', '34927384', '34927566', '34927830',...
3
votes
1answer
321 views

Suspect (Torn) Pages regarding tempdb?

I have a virtualised SQL Server 2005 (SP3) that reported the following message: Database_Name, Database_Id, File_Id, Event_Type, Event_Type_Desc, Error_Count, Last_Update_Date tempdb, 2, 1, 3, Torn ...
4
votes
1answer
209 views

Is there any way to add a witness without restarting the witness afterwards?

I want to add a third instance of SQL server to my mirroring pairs to act as a Witness. When I set the witness, the mirror remains disconnected from the witness and the mirror's log accumulates errors....
1
vote
1answer
109 views

Join a 'default' value only when there is no fully matching record

I need to be able to join to a 'default' row in a table (righttable) only when there is no matching record for both fields in lefttable. Below is the raw contents of the tables #lefttable ----------...
3
votes
7answers
39k views

Retrieving row count without using Count function

I wrote a query below which shoots me a syntax error why would it do so, SELECT MAX('Row') FROM (SELECT ROW_NUMBER() OVER(ORDER BY ID DESC) 'Row' FROM USERS) Error Desc: Incorrect syntax near ')'....
1
vote
1answer
20 views

In place upgrade from Express 2005 SP 4 to Express 2014, does database size limitation follow?

We are currently attempting get around a file size limitation issue with our database. The database file size hovers around the 4GB mark (the database size limit is 4GB in SQL Express 2005). We always ...
0
votes
0answers
85 views

Error when deleting file using xp_delete_file on SQL Server 2005

I'm trying to delete old backups with a scheduled job in SQL Server 2005, running on Windows Vista. Here's my script: DECLARE @BackupPath NVARCHAR(1000) DECLARE @DeleteDate DATETIME SELECT @...
22
votes
1answer
23k views

Primary key with “NOT FOR REPLICATION” option

I have recently taken over a project, and I have discovered that in most tables the primary key has the property "NOT FOR REPLICATION". I'm no DBA, but surely in most databases, a record without a ...
74
votes
5answers
72k views

What risks are there if we enable read committed snapshot in sql-server?

I have read here that some extra data will be stored per row so we might see a performance degradation but what other risks are there? eg. Will this affect recovery of the database? Is there anything ...
3
votes
1answer
3k views

Merge reports 'You must rerun snapshot because current snapshot files are obsolete', gets caught in Death Loop

We have a very large Merge publication for a data warehouse with some large (.bcp files on the order of 20GB) fact tables that is causing us tremendous trouble. Specifically, we created a publication ...
1
vote
2answers
65 views

Error while running sp_blitz in 2008 SQL Server and lower versions

I have created an SSIS package to run sp_blitz in my production servers. The stored procedure runs fine for servers having a version higher than 2012 but fails for 2008 and 2005. Please find the ...

1
2 3 4 5
27