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
0
votes
0answers
13 views

Does anyone know why the differential backups from SQL Server 2005 will not restore properly on SQL Server 2016? And how can I resolve this? [closed]

We are planning to migrate from old sql server 2005 to sql server 2016 with the differential backup but I read some article that the differential backup doesn't support properly .
2
votes
1answer
258 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 ...
0
votes
1answer
90 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, ...
4
votes
1answer
142 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
46 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
37k 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 ')'....
11
votes
7answers
46k 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
19 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
40 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
21k 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 ...
73
votes
5answers
69k 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
47 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 ...
27
votes
6answers
127k views

SQL Server database size didn't decrease after deleting large number of rows.

I'm no good in SQL, but I've got a database to maintain. There's almost no place left for it, so I've decided to delete all the data for, let's say, year 2008. After executing delete query (had ...
13
votes
5answers
13k views

In SQL Server, is there a way to determine the values of the parameters passed to an executing stored procedure

One way to determine the executing stored procedure is to use "dynamic management" methods, like so: SELECT sqlText.Text, req.* FROM sys.dm_exec_requests req OUTER APPLY sys....
3
votes
2answers
912 views

Execution plan flips Filter and Execute Scalar when using the PK, causes cast to fail

I have some data tables that where imported in from a flat file source so everything is varchar(max) except FileRecordID which is a int that hold the row number the data came from in the flat file ...
13
votes
2answers
2k views

SQL Agent powershell context reference

At my new job, we have multiple named instances on each servers. e.g. Server1\Dev Server1\DevIntegrated Server1\QA I have a SQL PowerShell script in the works that calls out to the OS, invokes Foo....
19
votes
3answers
24k views

How does shrinking a SQL Server log file affect performance?

I have a SQL Server 2008 database that has a data file of some 2GB in size, but the log file is over 8GB. With pre-2008 databases I could use the 'Backup log' and the TRUNCATE_ONLY option but this is ...
7
votes
9answers
35k views

Error Authenticating Proxy Account when Executing SSIS job

I have a SQL Server instance that runs 5 scheduled tasks each night, each of which run SSIS packages. These packages have been running for years and the associated steps run via a Proxy Account (...
-3
votes
2answers
25 views

Select all records from 2 separate tables [closed]

I have 2 separate tables with no links between them. A customer table and a product table. For each customer I wish to create a new row for each product in the product table I want the results to ...
-1
votes
1answer
714 views

How to drop partitioned index in SQL Server 2005

I am unable to drop partitioned index due to partition scheme and partition function existence. Table has a Primary Key, a Clustered Index and non clustered indexes. I am running the following query:...
9
votes
4answers
16k views

Do I really need to keep .LDF files?

Each month we make an end of month snapshot of our production database. These month end snapshots are strictly for reporting purposes, there are no inserts, updates or deletes ever done on them. Each ...
3
votes
1answer
300 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 ...
0
votes
1answer
48 views

Count SQL Query [closed]

Hi all i'm just wondering if anyone could suggest the easiest way to write this query. The problem with writing the subquery HomePageHits this way is that is bring the same records for the Hits and ...
1
vote
2answers
642 views

How to Backup Remote Database to FTP server

I can connect to a remote SQL Server via Management Studio. But, when I create backups, they get created on the server's disk. How can I transfer them to an FTP server? I only have a login to the MS ...
11
votes
1answer
3k views

Non-yielding IOCP Listener

Does anyone know what "Non-yielding IOCP Listener" indicates? On of our SQL Servers just had a bugcheck dump: ===================================================================== ...
19
votes
3answers
33k views

EXCEPT operator vs NOT IN

The EXCEPT operator was introduced in SQL Server 2005 but what is the difference between NOT IN and EXCEPT ? Does it do the same? I would like a simple explanation with an example.
7
votes
3answers
13k views

how to test if XML = '' in sql server?

I am working with sql server 2005 and I had a situation where many values can be passed in a parameter. Based on this: Passing multiple values for one SQL parameter this procedure uses XML as a ...
7
votes
5answers
709 views

Improving SQL Server performance on VMware under Linux

I have a Linux server in a small company (3 users) acting as a domain server and running two VMs under VMware: one has SQL Server 2000 on it and one has SQL Server 2005, in each case running on Win2k....
6
votes
2answers
13k 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 ...
14
votes
2answers
25k views

Find which session is holding which temporary table

We have a SQL Server 2005 database the temp database has become full. By going into SQL Server Management Studio I can see all the temporary tables in the tempdb. Is it possible to tell which session ...
28
votes
5answers
80k views

How do I run a large script with many inserts without running out of memory?

Question: I have a script with around 45 thousand insert from select statements. When I try and run it, I get an error message stating that I have run out of memory. How can I get this script to run? ...
0
votes
0answers
21 views

Audit Trail Settings in SQL Server 2005

Where I can find the Audit Trail Settings in SQL Server 2005. Google search gives link to the topics like "how can we create audit tables", but I just need to know where I can see the current settings,...
22
votes
4answers
72k views

Grant permissions to run an SQL server job

I have a job on my MSSQL server 2005, that I want to allow any database user to run. I'm not worried about security, since the input to the job's actual work comes from a database table. Just running ...
7
votes
4answers
13k views

SQL Server has encountered 1 occurrence(s) of cachestore flush

On one of our production server log files, the following message is being observed on daily basis. What does it mean? Is it a serious issue? SQL Server has encountered 1 occurrence(s) of cachestore ...
2
votes
3answers
515 views

SQL Server 2005 merge replication failure

We have a customer who, when trying to sync a client (subscriber) to the server (publisher,) receives the following error: 2012-11-30 21:03:33.334 Percent Complete: 18 2012-11-30 21:03:33.334 Data ...
1
vote
1answer
908 views

SQL Server Replication tools [closed]

We're trying to create an exact copy of our prod database to later use it for reporting. I was told that some of my colleges tried to use the out of the box replication tools that SQL Server ...
1
vote
1answer
2k views

Using the name of a table contained in a column of another table?

I have a table called MyTable with varchar columns named SourceID and ReferenceID, where SourceID contains a table name and ReferenceID contains a key value for the source table. Is it possible in a ...
5
votes
2answers
11k views

Cannot rename the table because it is published for replication

I need to rename some tables in my DB - SQL Server 2005, with this: EXEC sp_rename 'dbo.TableName', 'Tmp_TableName', 'OBJECT' but I can't do this - I get this exception - Cannot rename the table ...
16
votes
4answers
48k views

How to break SQL script execution

I am working on sql script and I am having a requirement of stop continuing the script if some conditions are not satisfied. When I Google it, I found the RaisError with 20 severity level will ...
5
votes
1answer
5k views

Unable to kill SPID

I have a SQL Server 2005 Std (x64) with SP4 that has a procedure I cannot seem to kill. If I view sys.dm_exec_requests I see SPID 103 with wait_type of LCK_M_SCH_M, a status of SUSPENDED and command ...
2
votes
2answers
774 views

Error -2146825284 / 0x800A0BBC when trying to write file

I have a query which uses sp_OAMethod @init, 'SaveToFile', NULL, @fPath, 2 to write from an image column to disk. It is failing with an error which I think relates to file permissions. -2146825284 / ...
1
vote
0answers
1k views

SQL Server Alert System: 'Severity 17 - Potentially Fatal Error Insufficient Resources' occurred on myserver

I get a alert email with the title: SQL Server Alert System: 'Severity 17 - Potentially Fatal Error Insufficient Resources' occurred on myserver as 12:02 when I connect to myserver to check the ...
6
votes
2answers
1k views

How do I automate converting heaps into clustered indexes?

I have around 40 tables in one of our production databases that, for varying reasons, where not created with a clustered index. What is the best automated method for converting these heaps? Since I'...
-1
votes
1answer
613 views

How to raise an alert when a table reaches a certain size? [closed]

I would like to set up an alert when the disk space used by a table reaches a certain size. I am aware we can set up an alert for file group\log file\database reaching a certain threshold. It would ...
3
votes
4answers
1k views

How does the database decide which Index to use

DDL create table t ( id int, id1 int ) create index Example_Index On t(id,id1) create index Example1_Index On t(id1,id) DML insert into t(id, id1)values(1, 100) insert into t(id, id1)...
4
votes
1answer
127 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. ...
5
votes
3answers
9k views

How do I use `sys.dm_exec_connections` in combination with SUSER_SNAME()?

How do I use sys.dm_exec_connections in combination with SUSER_SNAME()? I want to see the username corresponding to each record in sys.dm_exec_connections.
1
vote
0answers
160 views

Unable to Connect to SQL Server 2005 through ODBC drivers from Windows 2012 server

We are unable to connect to a specific Database server which has SQL Server 2005 installed on Windows 2003 server from any of the Windows Server 2012. It used to work till last week, the only change ...
5
votes
1answer
10k views

moving large number of tables to different filegroups

An existing database has a large number of tables stored in the PRIMARY filegroup. I want to move theses tables and their indexes on different filegroups automatically, depending on the "prefix" of ...