MS SQL Server :: Log File Is Full Error: How To Prevent This Happening?

Log File Full Stored Procedure Error
Does anyone know exactly what behaviour is exhibited when running astored procedure and the log file fills up while the procedure is stillrunning?I am seeing, through a created history table, a stored procedure thatbegan to run (by the fact that there is a row in the history table) butnot finish correctly. It is being called by VB and no user isreporting that VB is erroring out.Checking some of the SQL log files, the times the SQL proc bombed isaround the same time the SQL transaction log had to be manually dumpedbecause it was full.Thanks,

View Replies !

DTS Package - How To Prevent A 0 Byte File?
I've created a DTS package -- that uses a query to export to a .txt file. My question is -- if the results of this query are zero (no results returned within the package ) -- how can I tell the package not to export a zero byte file. Any thoughts on that? Any help you could give would be greatly appreciated. Thanks!

View Replies !

Prevent SSIS From Creating An Empty Flat File
I created a SSIS to export to a flat file (from a SQL command : a stored proc).
I don't wan't my SSIS to create an empty file if there is no data.

How can I achieve this ?

Thanks,

Vince

View Replies !

How Do I Prevent A SQL Error Terminating My Package?
I am getting the ubiquitous error....

"The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

I have a stored procedure that performs a task on a loop. Sometime it fails, somtimes it succeeds and that's just fine, the errors are handled within the stored procedure and I return counts of success and failures.

The trouble is that the package execution terminates when even one of the task fails. I don't want it to, I just want it to continue as the pakage should then go on to extract the source of the failues and post them back where they came from.

Any ideas how I can achieve this?

I have tinkered with the MaximumErrorCount setting, but I get the same result regardless of whether this is set to 0, 1, or 99999.

I also have a 'failure' path leading from the SQL task, but the package does not follow it.

I'm using an OLE DB connection and when none of the tasks fail, the process works perfectly.

Any ideas, most appreciated.

View Replies !

Prevent Error Being Thrown When No Messages Exist
I am using the following code to check whether a message exists on the specified queue:

Code Block
DECLARE @RecvReplyMsg NVARCHAR(100);
DECLARE @RecvReplyDlgHandle UNIQUEIDENTIFIER;
BEGIN TRANSACTION;
RECEIVE TOP(1)
@RecvReplyDlgHandle = conversation_handle,
@RecvReplyMsg = message_body
FROM TargetQueue;
END CONVERSATION @RecvReplyDlgHandle;
SELECT @RecvReplyMsg AS ReceivedReplyMsg;
COMMIT TRANSACTION;
GO

How can I prevent errors being thrown when there are no messages waiting on the queue? For example, if I send a message to the queue and run the above twice I get the following:

Msg 8418, Level 16, State 1, Line 11

The conversation handle is missing. Specify a conversation handle.

Thanks

View Replies !

What's Happening To This?
I have 2 aspx pages. one is "login.aspx" and the second is "test_connection.aspx". the "login.aspx" is using the membership class for my website's security. if u have have restarted your computer and you first load this "login.aspx", this will work fine and you will see that you can create a user. when you load (or view) next "test_connection.aspx" you will get this error message:

System.Data.SqlClient.SqlException: Cannot open user default database. Login failed.

Login failed for user 'sqluser1'.

at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

If I will restart my computer and I will load first the "test_connection.aspx", this page will work just fine and if you will load next "login.aspx", i will get this error message:

Cannot open user default database. Login failed.

Login failed for user 'YECIAASPNET'.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Cannot open user default database. Login failed.

Login failed for user 'YECIAASPNET'.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

the page "test_connection.aspx" is using an sql username i have created. I am only using one database in this project and thats it ASPNET.mdf

what's happening? i cant understand. I dont know what im doing wrong. i am very new to this dotNET and MS-SQL. im not sure if this has relation to the other problem i have on the other thread (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=254568&SiteID=1).

PLS HELP.

View Replies !

Logging Not Happening
Hi,

I set up my package for logging to SQL Server. I set up a connection manager for the logging, but did not specify a database. Doesn't that mean that the logging should default to msdb.sysdtslog90?

But when I check the table, there's nothing in it, after I run the package.

What gives?

View Replies !

Insert Is Not Happening
Hi,

Im facing one problem When more than one user tries to insert record through the application only one users data is getting inserted. Wat might be the problem?

This is the stored proc im using..

create
proc [dbo].[GSK_insertregion] (@Country varchar(50),@Userid int,@RegionId varchar(50))
as
begin
insert
into UserHierarchy(Country,Userid,RegionId) values(@Country,@Userid,@RegionId)
end

Please help..

Sundaresan.R

View Replies !

No Transactions Are Happening
i was working on a production server, and have stopped the sqlserver service along with the sql server agent. since i had to copy a MDF file . now i started the service again . but i find that there are no transactions happening ....what could be the reason.

View Replies !

Database Connection Not Happening. Please Help!
Hi
I have deployed a website on a server having Windows2000, IIS5.0 . It uses SQL Server 2000 which is on another remote server. While developing I used the visual tools in VS.net to make a connection and have used DYnamic properties of the connection object to map the connection string to the entry in to the config file.
This works fine on my developement machine which has IIS and SQL Server 2000 on the same machine.
The entry in the web.config for my connection string is:

value= " server=xxx.yyy.com; Trusted_Connection=yes;provider=SQLOLEDB.1;Initial Catalog=events; User id=myuser; Password=password;"

where xxx.yyy.com is the server running SQL Server2000.

I do not get any error but the conncetion doesnot happen and my datagrid doesnot get filled.
The code for creating the connection is designer generated code.
Any clues?
-svp

View Replies !

Tables Seem Too Big - Timeouts Happening
I have two problems I need some help with.First, I've just inherited a system and am delving into a few timeoutproblems that are causing problems for the users.Now, if I do a simple select * from the table (which looks to be thecause of the problem at this stage) in QA, I get the results back inless than a second. If I open the table in EM it takes about 10. Isthere a difference in viewing the data this way ? I'm used to EM beingvirtually the same speed. There is only one row. Minor questionreally, just something I'd like to understand if there is adifference.CREATE TABLE [QUERY] ([QUERY_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,[CAT_ID] [numeric](18, 0) NOT NULL ,[QUERY_DESCR] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL ,[USER_NAME] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL ,[USER_ID] [int] NOT NULL ,[IND_EURO] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULLCONSTRAINT [DF_QUERY_IND_EURO] DEFAULT ('N'),[IND_DGCOLUMNS] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL CONSTRAINT [DF_QUERY_IND_DGCOLUMNS] DEFAULT ('N'),[NO_GROUPS] [int] NOT NULL CONSTRAINT [DF_QUERY_NO_GROUPS] DEFAULT(0),[NO_FIELDS] [int] NOT NULL CONSTRAINT [DF_QUERY_NO_FIELDS] DEFAULT(0),[NO_LINES] [int] NOT NULL CONSTRAINT [DF_QUERY_NO_LINES] DEFAULT (0),CONSTRAINT [PK_QUERY] PRIMARY KEY CLUSTERED([QUERY_ID]) WITH FILLFACTOR = 90 ON [PRIMARY] ,CONSTRAINT [FK_QUERY_QUERY_CATEGORY] FOREIGN KEY([CAT_ID]) REFERENCES [QUERY_CATEGORY] ([CAT_ID]) ON DELETE CASCADE ON UPDATE CASCADE) ON [PRIMARY]GOI don't think any re-indexing has been done on this (or the othertables in the db). I was wondering if constant adding/deleting rowscould cause the index to be massive and in need of a good clear out.Any pointers would be appreciated. From what I can tell, there wassome problems trying to get replication to work. I need to dig deeperto see if this is now correct.-------------------------Secondly, there is a another table in the same database.CREATE TABLE [FIELD_DATA] ([ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,[DATA_ID] [numeric](18, 0) NOT NULL ,[FIELD_ID] [numeric](18, 0) NULL ,[FIELD_CODE] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL ,[FIELD_VALUE] [numeric](15, 5) NULL ,CONSTRAINT [PK_FIELDDATA] PRIMARY KEY CLUSTERED([ID]) WITH FILLFACTOR = 90 ON [PRIMARY]) ON [PRIMARY]GOIt holds approx 4 million rows. The rest of the tables have minimaldata and about the same amount (consider them the same if you will).Now, another 'copy' of this database is held elsewhere (differentclient data) and this holds 40 million rows. The difference is thatthe first DB is 4.5GB and the second 6.5GB (approx). Does this provemy theory that re-indexing would be a good idea ?ThanksRyan

View Replies !

Strange Things Happening!
Hi I am trying to use a Access to link to tables in a SQL server database. However it is doing some of the strangest things. Persently now I have copied the backend from the access database into a SQLserver and then I linked all the tables, but the forms are showing anything at all, if I login to some seperate forms it seem to pick up the content of the table and if I log onto the tables singularlyu it semes to be fine. It just all the usual login and startup form just show gray screens.

Does anyone have any idea what could be causing this and how I could fix it , thanks Ed

View Replies !

Mysterious Backups Happening
I'm no guru but this one has me stumped!!

Each day there is a backup occurring of a database but nothing is scheduled either as a Job or under the database backup scheduling option.

Any suggestions greatly appreciated.

Thanks.

View Replies !

What Will Be Happening To The SSRS While DB Mirroring?
hi there,

can the SSRS works with DB mirroring?
If yes, how to do it?

I just tried to enble mirroring for reportserver and reportservertempDB, but it doesnt work after failover.
Any idea ?

View Replies !

Can't Find Out Where This Insert Is Happening.....
Hello people, I don't expect anyone to know the answer to this but I guess we'll see huh?

I'm using Microsoft SQL Server Management Studio to do all my SQL stuff, and one of the tools that comes with this is a program called Microsoft SQL Profiler 2005. Anyways, so I'm using this profiler to capture SQL processes in the background while I work with the front end.

Here is a problem I'm facing... I am looking at this table right... and I see that when I do this certain process on the front end, it inserts 3 rows into the table. So I'm thinking "I know that on the tracer, I should be looking for an insert or a Stored Procedure with and insert in it."

So I use the tracer on the front end process and it shows me all the stored procedures that happen during the process of inserting these 3 rows into this table (btw this process is doing other things besides inserting stuff into this table, but it's what i'm currently working with at the moment.)

So what I do here at my job is I take this code, tweak it to work for our front end, and wah lah.... we're good to go. So in saying that, I make my own stored procedure and I all these stored procedures that are happening.

My result......

I get only 2 row inserts into the table.... the 1st row and the last row..... the middle row isn't inserted for some mysterious reason. I tried checking all the stored procedures for unique information pertaining to that specific row insert but to no avail I couldn't.

So my question to you guys is.... is there anything I'm overlooking that could possibly be inserting that row in the table? Thanks guys!

View Replies !

Do You Know How To Solve This Event Warning From Happening?
18275 :
Unnamed tape (Family ID: 0xcfb8a96c, sequence 1) dismounted from tape drive ‘master tape backup’.

this also happens for the mounted part also, how do I name a tape drive? or stop these warnings in the event logs?

thanks for your help

View Replies !

Newbie Tears, Or What's Happening With DECLARE?

declare @old varchar(30); set @old = 'old';

Command(s) completed successfully.

select @old;

Msg 137, Level 15, State 2, Line 1

Must declare the scalar variable "@old".

Why?!

View Replies !

Reference Not Happening To The Report Header When Grouping Changes
Hi,

Iam having 500 pages report,the report ia running for the file key ,I have reference some of the feilds to the reports header segment to display in every page,For every file key the report header feild also will change ,It is working for all file keys but the reference not occuring from body to report header when file key grouping changes to other grouping

Please let me know is there any possible way to do this one

View Replies !

Data Selection Not Happening For A Date Because Of Time Format
hello all,

i am making a query which select the data again a particuler date.

I insert values in the table for with current date(Today's date) and the records is inserted with the date format(2006-07-14 16:12:09),now when i run the query after 2 or 3 minutes to select the records inserted today, my query returns no results.

I think it is because of the the time (14:16 in this case) that after 2 minutes, the query looks for the records inserted at (2006-07-14 18:12 or 2006-07-14 19:12) and does not get the result.

Is there a method to not consider the time(14:16) when running the query but the query fetches the records including the records inserted at this time(14:16) no matter at what time I run the query today?

Please anyone help me!

Thanks in advance!

View Replies !

The Logon Page Running Successfully But Redirect To Folder.aspx Not Happening
Hi everyone,
I use the forms authetification for my report manager and server. But, i have this problem :
The Logon page running successfully but redirect to Folder.aspx not happening

To clarify:

>I get the logon page (UILogon.aspx)

>My user has been registered ok (i have checked the entry in the db to make sure it was created)

>I enter the login & password correctly and page posts back

The redirect never happens - In the browser, it never leaves the UILogon.aspx

Using Win2003, SQL Server 2005, Reporting Services

Any help greatly appreciated.

Thank you.

Shaouk.

View Replies !

Why The Transaction Log File Full
I have set the initial size of the log file for a database to 1M, themaximum size is unrestricted, and the increase rate is 10%.However, when I attempt to delete thousands of rows, the error is stillreported that the transaction log file is full. Why can't the log fileincrease automatically?*** Sent via Devdex http://www.devdex.com ***Don't just participate in USENET...get rewarded for it!

View Replies !

Primary File Is Full
We set the database to : automatically grow file, unstricted file growth, grow by 10%.
As I understand it will automatically grow, why we recieved error message like primary file full.

View Replies !

Help Me! The Log File For Database Is Full
Hi,
I´m working with a sql server 2000 bd and i have a bd with simple recovery model. Each day i have the next error:

"The log file for database x is full. Backup the transaction log for the database to free up some log space"

I tried to limit the transaction log file to 500Mb but then I have this error. I have done the reduction manually of transaction log file but the next day i have got the same error. If i don´t try to limit, this file grows a lot of (1GB) and then i haven´t got enough disk space. Can you help me, please?

Thanks a lot.
Memupi

View Replies !

Tempdb Log File Is Full

My Tempdb log file is getting full very frequently. I could see that tempdb log file is not getting truncate automatically since checkpoint is not occuring as execpted.

If a shrink the tempdb its getting truncated immediately and releasing the full occupied space.

So i come to an conclusion that auto checkpoints are not happening even though the tempdb is in SIMPLE recovery model.

I search in KB and could find the article related to this error.

http://support.microsoft.com/kb/909369/en-us

I would like to get it confirmed is the article described is the same issue i am facing. Also if you could let me know the hot fix details for this, that would be great.

View Replies !

Sql 3.5 Event Log File Full Help Please
event log file is full

i am new to visual studio 2008, but comprehend pretty well. (i use to steal code from .bas files and whatnot in 5th grade in vb4 for aol ''progz'') anyway, all i wanna do is create a simple single form window as my first project with vs 2008. i have a huge list of movies, and i would like a form to help me list what titles i have, and their location (friends house, cd book 1, book 2, movie tower, etc.) and i would like to be able to add to the list, delete, edit location, from my form. right now i got as far as designing my interface, and started to add a database. i cant even get pass that because..

i goto add item from my menu bar..
add item..
local database..

then the configuration wizard comes up and says an error occured while retrieving the information from the database:

the even log file is full.

help???

View Replies !

What If Msdb Log File Full?
WHAT IS MSDB LOF FILE IS FULL?PLEASE HELP ME ON THIS ISSUE

View Replies !

Primary File Group Full
Weird. I have an Agent job that populates some warehouse data every night. It's been working fine for over a year. Then it fails with a message that thePRIMARY file group is full. The database is set to Simple recovery, automatic, unrestricted growth by 10% for both the log file and the data file. The drive it's on has 96GB free. The data file is 1.5GB and the log is 2MB and there's not very much fluctuation in the amoutn of data going into it.
Anybody have an idea why that would happen?
Thanks for any insight,Pete

View Replies !

Log File For Database 'tempdb' Is Full
HiI am getting this common error once or twice a day:Error: 9002, Severity: 17, State: 2The log file for database 'tempdb' is full. Back up the transactionlog for the database to free up some log space.provided......1. My log file drive has more than 20 GB free out of 30 GB2. Both data file & log file has default setting on unrestricted filegrowth by 10%3. Currently we moved from SQL 7.0 to SQL 2000 & the load in the userside also doubled4. We can't do the temporary solution like restarting the server orSQL service, because the application is a real time system with muchless manual interaction.Thanks in advance.RegardsSeni

View Replies !

PRIMARY File Group Is Full
Hi there,I've just run some DTS packages on my test sqlserver (Which has limitedhard disk space and memory) and all the tasks have failed, due to'PRIMARY' file group is fullIs there a query or script I can run to resolve this problem??M3ckon*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View Replies !

Sql 2k Database Log File Full ... How To Empty That
hi,my sql database log file has been fulled recently ..... becuasethere are 55 millions records in main 3 tables .... so how i can emptylog file ...i don't want to attach new log file or save any pervious log info.....thanks for helping me ... and my company ..Abdul SalamSr. DBA + ProgrammerXebec Groups of Business.

View Replies !

Database File Group Is Full
We have a database that is set to autogrow, 10% increase.

I want to know when it will autogrow, like what % left then will it autogrow?

Also is it possible for above settings, I can in some way set up an alert that tells me the database file group--not log file is 90 % full?

Thanks

View Replies !

My Log File Has Grow The Disk Full
My logfile has grow the disk full - the logfil is 25 gb and I have 4 gb free.

I can't shrink the log fil !

Can I set the log file to null ??

I have backup my datafil successfully!

Help!

View Replies !

How Can We Find When The Data File Is Full?
I need to find out when the data file and transaction log file is full. Is there any stored procedure that will let how much space left. We don't want to set Autogrow for the files.

Thanks,
Rau

View Replies !

The Default File Group Is Full
Hi folks,

I got a message that 'Could not allocate space for object '(SYSTEM table id: -732777483)' in database 'TEMPDB' because the 'DEFAULT' filegroup is full.
Connection Broken'

Can you tell me how to solve this problem?

Thanks in advance.

Mike.

View Replies !

Primary File Group Is Full
Primary file group is full for one user db
This is production server
Total db size is 132186 MB
data file is 12000 MB, set automatically grow
and restrict file growth is 121024 MB

Now there is 30 GB space on drive

I have added space to file 3 times(1GB, 10GB, 3 GB) and incresed restrict file growth also(5GB). database is in simple recovery mode.

stille I am getting above error, pleasee let me know how to proceed now?

View Replies !

Primary File Group Is Full

I got below error :

Could not find allocate space for object 'tablename' in DB, because the primary file group is full

Upon investigation I found :

datafile is reached it's maximum size
DB Size space allowcated 197 MB, space free 20.69 MB
DB mdf 194880 KB, ndf 6272 KB

It's production server, please let me know how to proceed now?

View Replies !

Data File Size Full
I'm new to the DBA world, and have no one else in the company to look up to. Does anyone know what I might need to check out or do when the Data File Size is 204% full? Or is this not necessarily a bad thing?

I'm getting this from a Diagnostic tool I have.

The number of tables is 148
Data file size 35,941 MB
Data Size 26,549.92 MB
Index Size 177,130.02 MB
Log File Size 5.05 MB

Thanks,

View Replies !

The Log File For Database 'tempdb' Is Full.
there is a sql job that failed yesterday. This job calls a store procedure. This store procedure doesn't use any temp table. But there are lots of updates and inserts clauses.

application log shows:
Error: 9002, Severity: 17, State: 2
The log file for database 'tempdb' is full. Back up the transaction log for the database to free up some log space.
----------------------------------------------------------------
tempdb.mdf 1.37gb
templog.ldf 19.6 mb

these files are located on D: drive and D drive has 52gb free space

databasename : tempdb
database_size: 1422.00 mb
unallocated space: 1403.60 mb

----------------------------------------------------------------
anyone to fix this?

i used "backup log tempdb with truncate_only" But i dont know if it truncated the log file?
how do i fix this problem.

View Replies !

Full-text Indexing On File System
I have several file in .doc, .xls, .pdf and would like to know if SQL Server can do a full-text indexing on these files? These are files are stored on different locations in th harddisk.
eg.
c: empmywork.doc
c: empscheduleschedule1.xls
c:showroomdatasheet1.pdf

View Replies !

PRIMARY File Group Is Full EMERGENCY!
Customer site has detached their database and deleted the .ldf file and reattached because they want to decrease the log file size. They set their log size to restricted file growth and 5mb. After about 20 minutes they started getting "Transaction log is full" so I had them up the transaction log to 50mb. After about another 20 mins everyone on the network started getting errors saying that data cannot be added because the PRIMARY file group was full ("Could not allocate space for object TrussLumber in Database ATP because the primary file group is full").

There is plenty of disk space so I know thats not the problem. And they can add data to other databases, so I cant imagine what could possibly be causing this....Can anyone help me out?

Thanks!
Craig

View Replies !

Full Text File Locations - Best Practices
Any suggestions and/or supporting documentation for best practices on where to locate the full text catalog files. Especially as related to other data, log, and tempdb files?

My searching is coming up empty.

Thanks in advance.

Terry

View Replies !

Recovery From Bak File W/ Full Text Problem
I am having a problem restoring to a new or different database than the backup file being restored was created from. I understand the Move commands, and have figured out how to get this to work by mapping the data and log files from the backup file to the files defined for the restore target database.

The problem is with the full text catalog that is part of the backup.

Here's the T-SQL I'm executing, and the error I receive:

T-SQL:
RESTORE DATABASE New_DB
FROM DISK = 'C:ackupOld_DB.bak'
WITH RECOVERY, REPLACE,
MOVE 'Old_DB' TO 'C:SQLDataNew_DB.mdf',
MOVE 'Old_DB_log' TO 'C:SQLDataNew_DB_log.ldf'

Error:
Msg 1834, Level 16, State 1, Line 1
The file 'C:SQLDataFTDataftKeyWords0007' cannot be overwritten. It is being used by database 'Old_DB'.
Msg 3156, Level 16, State 4, Line 1
File 'sysft_ftKeyWords' cannot be restored to 'C:SQLDataFTDataftKeyWords0007'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Not sure what to do about this, any help would be greatly appreciated!

View Replies !