What is best practise for what this number should be?
I have seen guidance saying set to a number greater than 25000, but not from any source I particularly know or trust. (I checked SANS, NIST-CIS and the NSA, but I couldn't find anything.
This is for SQL2k5. The database may be small or big, I don't know (it's going out to multiple customers). I'm wondering if in general it's considered "better" to create a single non-primary default filegroup and put all the objects there, or just leave everything in primary? In one training years back I got the impression that recovering the primary filegroup was important for certain restore operations, so it was always wise to separate them like this.
Thanks for your input,
Mike
MCDBA
In general, what is the most appropriate way to specify a "data driven" default for a report parameter. Seems to me like options are:
1. construct an expression which operates on one of the existing report datasets (possibly the same data set used to populate the defaults dropdown)
2. construct a new dataset which returns a single value which is the desired default
what's the "normal" approach?
For example, I have an MDX dataset which contains the list of dates for which data is available. There is generally some data available for today, but normally the user is interested in the most recent day with data prior to today.
So which would be the better option out of the two above (or a third option if I'm missing something!) to tell my parameter to set itself to:
"The most recent day prior to today for which data exists".
Hi There
In terms of scaling out Service Broker to hundreds of instances, would it be better from a performance perspective so have one queue with all the messages coming in(obviously with a high number for max_queue readers), or create a number of queues and spread the messages across them ? Or is there no significant difference.
The one reason i am leaning towords multiple queues is so that is poison messages are found or a something lese goes wrong with a queue not all messages are affected, however creating multipple queues makes it more complex and required more administration ?
Any general best practice when it comes to this ?
Thank You
I'm setting up a new server and would like the default location for the transaction logs to be on a different drive from the databases. Seems like the best way to do this would be to configure the Master database log location for the drive & directory where I want all trans logs stored. So far, I've been unable to accomplish this. I know I can specify the log location manually each time I create a database, but I'm hoping to configure a default. Is this possible to configure? And if so, how?
Environment is W2k with SQL7.
TIA, Sara
When looking at SQL Server error logs, I noticed that the current error
log had grown to 1MB whereas most of the files are only a few KB.
QUESTION 1: Can I set the max size for an error log file? If so, how?
QUESTION 2: If not, is there a workaround?
Thanks
I have Logs table and want to assign a serial number to the techs using the following query
Select
Date,
Case_ID,
Site,
Dept,
Tech,
Start_Time,
ROW_NUMBER () OVER (PARTITION BY Date, Site, Dept, Tech ORDER BY Start_Time) as Row_Num
FROM
Logs
Where Date = Getdate()
I get the following results.
Date Case ID Site DeptTechStart TimeRow_Num
7/28/14 10023 TartvilleMaintcAmy P.7:301
7/28/14 56789 TartvilleMaintcRem W.8:051
7/28/14 23098 TartvilleMaintcAmy P.8:352
7/28/14 70004 TartvilleMaintcAmy P.9:103
7/28/14 12708 TartvilleMaintcMag O.10:001
7/28/14 10004 TartvilleMaintcAmy P.12:304
7/28/14 40056 TartvilleServiceJoe F.7:301
7/28/14 23458 TartvilleServiceJoe F.7:552
7/28/14 69200 TartvilleServiceRus T. 7:301
Please notice the cases in Maintc department. See how Amy P.'s shift is broken by Rem W. and Mag O. But the Row Number does not recognize this, it still says Amy P's case as 2 and 4 the even though Rem's and Mag's cases were in between.
This is what I really wanted.
Date Case ID Site DeptTechStart TimeRow_Num
7/28/14 10023 TartvilleMaintcAmy P.7:301
7/28/14 56789 TartvilleMaintcRem W.8:051
7/28/14 23098 TartvilleMaintcAmy P.8:351
7/28/14 70004 TartvilleMaintcAmy P.9:102
7/28/14 12708 TartvilleMaintcMag O.10:001
7/28/14 10004 TartvilleMaintcAmy P.12:301
7/28/14 40056 TartvilleServiceJoe F.7:301
7/28/14 23458 TartvilleServiceJoe F.7:552
7/28/14 69200 TartvilleServiceRus T. 7:301
I tried many combination of columns for Partition by () and Order by () and the best I can get is the result at the top. How should I achieve it.
hai all,
I need to change the default port number of the SQL server from 1433 to some other port number. i know that it can be done thru SERVER NETWORK UTILITY. Can some one confirm that. It will be helpful.
Also i want to make sure that port to given is free port. how can i find that particular port is free.
Thanking in Advance, awaiting Quick response.
Signing Off
Venkatesh.V
Hello,I have SQL Server 2000 Enterprise Edition, and I'd like to change thedefault port to something else (we'll use 2033 for this example). I'vetried to connect from another computer on the same subnet using"ServerName:2033" as the server, but it doesn't connect. It will connect ifI change the Server to use the default port and change the server to connectto on the client to "ServerName", but either I"m not referencing the newport number properly or ????Any help you could provide would be great.Thanks In Advance!Rick
View 1 Replies View RelatedHi,
If there is no match in the matrix - it displays an empty cell. Is there a way to display some default number instead?
Thanks,
Igor
There's lots of smart people here who can, I am sure, offer insight on this one.
I need to add handling of erroneous data to my package.
This is what my SSIS package does. The source table has thousands of records in it, most of which have been processed already (have a status of "Finished".) A typical run would have 400-600 records to process. The runs happen overnight.
Steps:
1) Execute SQL task that Updates all records in input table with status "New" or "Error" to "Process"
2) Data Flow task that takes as input all records with status "Process" and outputs to destinate OLEDB table
3) Execute SQL task that Updates all records with status "Process" to "Finished"
Erroneous records are identified at step 2 (Data Flow task) and need to be marked as "Error" so that they are not marked as "Finished" by step 3. They will be picked up and reprocessed by step 1 next time around.
I am having trouble seeing what would be the best way to achieve this? I am concerned that any steps I take might be prone to deadlocking as the updated data is also the source data?
My initial thought is to output some data to an "error" table in step 2, and then insert another Execute SQL task before step 3 that will update the source table to mark all these records as "Error" records. Does this make sense? Is there a best-practice way to achieve this?
Dylan.
Hi All,
I need to send out email when error occurs in the package. Is it a good practice to put the send email task in the event handler? Then MaximumErrorCount is set to 1. But for some reason, some time I saw more than one email are sent out. Please advise. Thanks
I am currently trying to export from SQL to notes via NotesSQL.I have set up the necessary ODBC data sources & DTS job. I am encountering errors when I attempt to execute the DTS package . I.e "job failed"
- How do I drill down to aquire more detailed error logging on the package to find out exactly what's wrong with DTS?
Thanks
Paul
When I'm in EM, there is one log that when I click on it to view it hangs my EM. All other logs (current and others) I can click and view just fine. I'm on my way out to the server to see the log in the file directory but what could cause this?
Thanks..
Is there another way to restart the SQL Error Log without shutting down and restarting the MSSQLserver service?
View 2 Replies View RelatedHi ,
How can i create an error log txt file for the jobs at the sql2005? the same like it was in the 2000.
many thanks
I created a package in SSIS, and used the SQL Server Agent to run the package. I get an error 'Package Execution Failed' everytime I run it throught the SQL Server Agent.I don't get an error if I run the package from SSIS.
I right-clicked in 'Control Flow' and clicked Logging to add a new log. After I re-run this again in the SQL Server Agent, where do I go to find the error log?
OS: Windows 2012 Enterprise
SQL Server: 2012 Enterprise
I was wondering if there is any way all SQL Server error log entries could be automatically written to Windows Event Log.
I have recently uncovered a problem we are experiencing with SQL Server 6.5 Service Pack 4 (Don't ask) and wonder if anyone has seen this before, and maybe has a solution.
When viewing an errorlog either current or historic, via either enterprise manager of xp_readerrlog, occassionaly the network connection is dropped and the process runs out of control. The process is unkillable, as it is in a 'Critical Section' and does not respond to kill. The real problem is that the process is logging millions of errors of the form "...cannot send results to the front end..." at a rate of around 1GB / hour.
Any help would be much appreciated.
Cheers
BP
I was under the impression or i always got following entries in SQL Agent error log when server restart and there are some entries in sql error log as well which confirms that server restarted. but today when i was checking error log i saw same entreis in SQL agent error log but there were no entries in server error log. now i got really confused whether my server is restarted or not........... because if it is restarted then there must be some messages in server error log.....
can some one please let me know when and why following messages appear in SQL Agent Error Log
Information,[171] There are 9 alert(s) in the alert cache
Warning,[425] delay_between_response attribute (10 sec) of alert (9) is less than poll interval for this alert (20 sec)
Warning,[425] delay_between_response attribute (10 sec) of alert (8) is less than poll interval for this alert (20 sec)
Warning,[425] delay_between_response attribute (10 sec) of alert (7) is less than poll interval for this alert (20 sec)
Warning,[425] delay_between_response attribute (10 sec) of alert (6) is less than poll interval for this alert (20 sec)
Warning,[425] delay_between_response attribute (10 sec) of alert (5) is less than poll interval for this alert (20 sec)
Warning,[425] delay_between_response attribute (10 sec) of alert (4) is less than poll interval for this alert (20 sec)
Warning,[425] delay_between_response attribute (10 sec) of alert (3) is less than poll interval for this alert (20 sec)
Warning,[425] delay_between_response attribute (10 sec) of alert (1) is less than poll interval for this alert (20 sec)
Warning,[425] delay_between_response attribute (10 sec) of alert (2) is less than poll interval for this alert (20 sec)
Information,[170] Populating alert cache...
Information,[168] There are 3 job(s) [0 disabled] in the job cache
Information,[297] SQLServer Message: 0<c/> SQLServerAgent Monitor started successfully. [SQLSTATE 01000]
Information,[133] Support engine started
Information,[167] Populating job cache...
Information,[110] Starting SQLServerAgent Monitor using '' as the notification recipient...
Information,[193] Alert engine started (using Eventlog Events)
Information,[146] Request servicer engine started
Warning,[396] An idle CPU condition has not been defined - OnIdle job schedules will have no effect
Information,[174] Job scheduler engine started (maximum worker threads: 800)
Information,[129] SQLAgent$XYZ starting under Windows NT service control
Error,[364] The Messenger service has not been started - NetSend notifications will not be sent
Information,[124] Subsystem 'ANALYSISCOMMAND' successfully loaded (maximum concurrency: 800)
Information,[124] Subsystem 'ANALYSISQUERY' successfully loaded (maximum concurrency: 800)
Information,[124] Subsystem 'QueueReader' successfully loaded (maximum concurrency: 800)
Information,[124] Subsystem 'Merge' successfully loaded (maximum concurrency: 800)
Information,[124] Subsystem 'Distribution' successfully loaded (maximum concurrency: 800)
Information,[124] Subsystem 'LogReader' successfully loaded (maximum concurrency: 200)
Information,[124] Subsystem 'Snapshot' successfully loaded (maximum concurrency: 800)
Information,[124] Subsystem 'CmdExec' successfully loaded (maximum concurrency: 80)
Information,[124] Subsystem 'ActiveScripting' successfully loaded (maximum concurrency: 80)
Information,[124] Subsystem 'TSQL' successfully loaded (maximum concurrency: 160)
Information,[432] There are 10 subsystems in the subsystems cache
Information,[431] Populating subsystems cache...
Information,[339] Local computer is ABC running Windows NT 5.2 (3790) Service Pack 1
Information,[310] ** processor(s) and **** MB RAM detected
Information,[103] NetLib being used by driver is DBNETLIB.DLL; Local host server is ServerName
Information,[102] SQL Server ODBC driver version 9.00.2047
Information,[101] SQL Server ABC version 9.00.2047 (0 connection limit)
Information,[100] Microsoft SQLServerAgent version 9.00.2047.00 (x86 unicode retail build) : Process ID 8100
Error,[298] SQLServer Error: 2812<c/> Could not find stored procedure 'msdb.dbo.xp_sqlagent_notify'. [SQLSTATE 42000] (DisableAgentXPs)
Hi Everyone,
I am very new to Integration Services and need some help on how to capture any errors generated in the SSIS package when executed. Any idea how I can accomplish this?
Thank You
-Sam
How can a person view the error logs without being an sa?
View 1 Replies View RelatedHi,
I want to see my sql executive error log but it grown like anything.
How i have to trucate my error log. I cannot stop my server now.
i am getting following message. and also suggest me what i have to do not to grow like this means 56.01 mb size.
The sql executive error log is 56.01 mb in size and loading it will consume considerble memory and may take several minutes to complete.
Now i want to see my executive error log , pls tell me what i have to do.
--rk
The SqlServer2000 error logs go to Program FilesMicrosoft SQL ServerMssqlLog directory by default.
How can I change their location. I could change SqlServerAgent logs but did not find any help or way to change the location of these. Would anyone please help?
Thanks
Vinnie
how to write a error logs in notepad in sql server 2008
View 3 Replies View RelatedHi,
In my case I have to log the errors raised by any task in a package to either windows event log, text file or SQL server. Also I need to send an email notifications to a group of people telling them about the error.
Now can I use SSIS package logging for logging the errors into the required destinations. I mean right clicking on the package and selecting Logging, then adding the required log providers and enabling the events for logging into those. I think I have to upfront select the log providers to log the error, I will not have the liberty to log the error to the destination, the name of which is passed as a variable to the package. This is okay with me though.
Now what will a custom log provider help me to do in this case. Also can I somehow configure my package to call the send mail task everytime an error is raised.
Also, one more option can be developing a package that only does the error handling. It will take in the paramters or the error codes and descriptions, the destination to write to and a flag to send mail or not for that particular type of error.
What do you think? Kindly advise.
Thanks in advance for your help and time.
Regards,
$wapnil
Hi All! Looking for some help with SQL errors.
We are using SQL 2005. Our server has been failing (service just stops responding) several times a week. We don't have a DB admin, just me, the developer, so please be patient.
I can view all the errors in the existing trace through the Profiler, but not sure how to follow error to query source.
How do you use the trace logs to find the problem query?
I have two significant errors in my logs. One is Hash Warning and the other is Missing Join Operand. I'm not interested in the general causes of these types of errors, that info has been easy to find.
I don't know how to link or otherwise associate a particular instance of an error in the trace logs to the query that caused it to determine the query that is causing the trouble.
The traces are already on the server. I've never created a new trace. I believe they are generated automatically by the server...
Can someone shed some light on how these existing traces are being generated and how I can modify them to include the data I need? When I open an existing trace on the server and view the properties, events tab, there appears to be no way to select the column "TextData" (no checkboxes) for the two errors we are seeing (listed above).
If the TextData column for the errors will allow me to see the offending query, can someone please tell me how to enable that column in my traces that are currently being generated on the server?
Thank you!
Drew
I am trying to install the SQL7 server, at the end of the installation before
rebooting the server I got the following error:
"Setup failed to configure the server. Refer to the server error logs and cnfgsvr.out for more information". I have not seen that error before. I cannot install the SQL7 because I always get the same message. Your help would be very appreciate helping me to solve this problem.
Thanks.
I have scheduled a database backup at 9 pm and 5 Transaction Log backups. The transaction Log backups happen at 11 am, 1 pm, 3 pm and 5 pm (device does not get initialized) and one at 11 pm where i initialize the backup device. now i have another server with the same database on it. I restore these backups on to this server by simple copying the files and then doing Restore ...From Device...Add file...option. I am able to restore the DB backup but when i try to restore the log backups it get the error msg -
'Specified file 'G:...' is out of sequence. current time stamp is Feb 9 2000 3:54 pm while dump was from Feb 9 2000 3 pm.'
When i am restoring the logs i start from the 1st backup buyt still i get this error. Can someone pls let me know what the problem is and how to resolve it. Thanks.
I have seen a couple of cases where an error occurs on a server running SQL Server 2005, and very quickly the log folder at MSSQL.1MSSQLLOG starts filling up with files, and does not stop until the entire hard drive is full (at which time the server stops responding). Is there any way to limit the number of .dmp files that are written?
View 7 Replies View RelatedDetails:MS SQL 2000 dual Intel 1.2 GHz processors.1 GB RAM2.1 GB dBDynamic Memory Managment.No other apps running on this server.First question:Since I have Dynamic Memory Managment setup, Is it usuall that thesqlsrv.exe process on the server steadily climbs and is in the 800 to900 MB range. There is only about 20 MB free. In theory this is howDMM can work, but do people really see it work this way.Second question:I had users complaining about lockups in the app I have to supportthat connects to this dB. At first I thought it was the large use ofmemory, but once I was able to see in Enterprise Manager that therewas process blocking several other processes. EM then locked up and Icouldn't get to the details of what the exact process was that wasdoing the blocking. After restarting SQL services things were fine.When I checked the logs there was nothing there about a hung process.The logs seemed very sparse. Why would there not be anything in thelogs about it. The logs actually seem very thin on any information.Thanks,T.
View 1 Replies View RelatedI've written a custom script to delete backup files from location. But unable to modify now to count the number of files are deleted. How to modify the script...
/* Script to delete older than N days backup from a specific directory */
USE [db_admin]
GO
IF OBJECT_ID('usp_DeleteBackup', 'P') IS NOT NULL
DROP PROC usp_DeleteBackup
GO
[Code] .....
Q1: Any way to control which db loads when, and perhaps stop this error from happening? Again- when all is said and done, the report server functions, and I am able to access just fine.
Q2: What kind of recovery is being referred to? Given the message type- I assume it's nothing of concern- probably more to do with a system that doesn't run all the time.
Okay- so I get these two event messages each time I boot.
Below are two error message I see daily with each system boot- the services mentioned do "actually" function, and are running- as not 10 seconds after these messages are logged, the successfully loaded messages appear. It's almost as if report services tries to connect before the databases are up and running.
Basically the sequence (today) was:
1. 05:49:01 Failure Audit (MSSQL$SQLEXPRESS)
2. 05:49:01 Error (Report Server Windows Service (SQLEXPRESS)
3. 05:49:02 Recovery is complete. (EventID 3408 MSSQL$SQLEXPRESS)
4. 05:49:06 Starting up database 'ReportServer'
5. 05:49:09 Server Resumed execution (MSSQL$SQLEXPRESS)
I'm assuming based on the events that Report Server Service attempts to connect to the ReportServer database before it's actually started. Doesn't really explain the failure audit-
1. Failure Audit:
Event Type: Failure Audit
Event Source: MSSQL$SQLEXPRESS
Event Category: (4)
Event ID: 18456
Date: 8/9/2007
Time: 05:49:01
User: <MACHINENAME><LOCALUSER>
Computer: <MACHINENAME>
Description:
Login failed for user '<MACHINENAME><LOCALUSER>'.
[CLIENT: <local machine>]
This is almost immediately followed by this (which I consider a no-brainer, considering the above):
2. Error:
Event Type: Error
Event Source: Report Server Windows Service (SQLEXPRESS)
Event Category: Management
Event ID: 107
Date: 8/9/2007
Time: 05:49:01
User: N/A
Computer: <MACHINENAME>
Description:
Report Server Windows Service (SQLEXPRESS) cannot connect to the report server database.